Forum Discussion

rodneyc8063_1's avatar
5 years ago
Solved

DAX CALCULATE - Using only Measure Expression with NO filter - How does it work?

So I have been trying to learn/study Power BI and "thought" I had a good decent grasp on DAX and CALCULATE that is UNTIL I came across a sample question that totally threw me for a loop.

 

I am going through the Microsoft Power BI Official Practice Exam from Measure Up currently. 

 

A question states that we need to create visualizations based on some financial data (Here is a sample excel file from Google Drive)

 

We need to create a measure for the average profit for products for use in visuals. We need to ensure that the measure you create will behave correctly when used with the CALCULATE functions. 

 

Its a drag and drop question that asks you to write a piece of DAX code - The answer is as follows

 

Average Product Profit = 

AVERAGEX (

      KEEPFILTERS(VALUES('financials'[Product])),

      CALCULATE(AVERAGE('financials'[Profit]))

)

 

So I am confused about a few things by the code above that I was hoping for some help to understand

 

For the line: [ KEEPFILTERS(VALUES('financials'[Product])) ]

 

I was under the assumption that KEEPFILTERS would only be used within CALCULATE.

 

So I usually see [ CALCULATE (<MEASURE>,KEEPFILTERS<filter>) ] 

 

I tried to play around with the data in PBI and remove the KEEPFILTERS and only keep the "VALUES('financials'[Product])" and the output seems to be the same. So Im not sure why KEEPFILTERS is even needed here?

 

Secondly, my biggest confusion is with the CALCULATE portion of the formula as shown

 

CALCULATE(AVERAGE('financials'[Profit]))

 

Usually again, I am used to seeing CALCULATE(<Measure>, <Filters>.......)

 

I havent seen JUST the CALCULATE(<Measure>) on its own

 

Based on the Microsoft documentation (https://docs.microsoft.com/en-us/dax/calculate-function-dax) I see that it is possible to only use CALCULATE(<Measure>), but the documentation isnt clear to me as to why or how to use it like so. 

 

Again I tried to play around with the data in Power BI and I couldnt quite wrap my head around why or when to use JUST the CALCULATE function with no filters?

 

Sample .PBIX file

 

For example, I tried to create two measures

AVG_1 = AVERAGE(Sheet1[Profit])
CALC_1 = CALCULATE(AVERAGE(Sheet1[Profit]))
 
As can be seen in the screen shot below it doesnt seem to make any difference either using CALCULATE with AVERAGE or just AVERAGE
 

 

Then I tried to use the formula exactly as shown

 

 

Right beside the same formula but without using the CALCULATE

 

 

Each row total looks the same, the difference comes in the row total.

 

It looks like for the column "Average Product Profit" its taking the average of the values shown

 

So -> 247500+137020.75+142091.88+90540+113294.42=3459263/5=146089.41 - Which is basically an average of the 5 products. 

 

Then "Average Product Profit 2" seems to be taking [ sum of all values / 13 = 1794373.75/13 = 138028.75 ] -> Which is the total number of values. 

 

So using CALCULATE in isolation as a column measure doesnt seem to do much. But when using CALCULATE in an iterator function (AVERAGEX) it seems to change things a bit?

 

I just cant quite wrap my head around what is going on exactly

 

Any insight or advice would be very much appreciated

4 Replies