Power BI DAX: Percentage Calculations with DAX Functions and Operator

In Power BI to calculate percentage measures or columns we may use DAX divide operator (/ - forward slash) or function “DIVIDE”. For example in my demo file in table “Financial Report Unpivot by DAX” we have Values of “Profit” and “Gross Sales”. For measure Profit Margin percentage creation we can use Operator forward slash or function “DIVIDE”, especially when we are sure that the denominator “Gross Sales” value is not empty or zero we should see same returned values from both profit margin measures.

in such situation the operator calculated measure should have better calculation performance than function calculated measure as operator does not process the “alternative” default query plan which is required in “DIVIDE” function.

Come to the situation when the denominator has zero or empty value, for example in our demo file when we want to calculate a measure “sales growth % “ over last year same period, we may do not have any sales value in last year same period to be compared. If we use operator forward slash to calculate the sales growth r% as the shown DAX formular we may end up some error or return a “infinite” value. Such kind error or value will bring the extra efforts to us for identifying and clean-up before our further analysis.

But when we use the Function “DIVIDE” with careful selected alternative value , such as “blank” in our example DAX formula we may avoid error or unwanted infinite values.

Keeping operator forward slash with enhanced function to calculate the percentage measures, such as combining IF or SWITCH functions, we may also able to avoid unwanted return values. For example, in our demo DAX formula with enhanced IF function we get same returned values as DIVIDE function created percentage measure , and DAX formula with enhanced SWITCH function and we also have same result as DIVIDE function created measure

Thanks for your watching, if you like to watch more latest videos from my channel please press subscribe button. See you in next video.

- References:

DIVIDE function (DAX) - DAX | Microsoft Docs

DAX operators (/ forward slash) - DAX | Microsoft Docs

For more info of my BI & Analytics, please refer to the following website links:

My BI & Analytics Website:

My blog website :