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

[rodneyc8063]

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

[jdbuchanan71 · Accepted solution]

@rodneyc8063 
The KEEPFILTERS(VALUES('financials'[Product])) doesn't really makes sense to me either, that one seems odd.

The CALCULATE inside the iterator forces context transition so the row context (the row being interated) is converted into a filter context.  Take a look at this article that discusses the topic.
https://www.sqlbi.com/articles/understanding-context-transition/

[AlexisOlson]

That use of KEEPFILTERS looked strange to me too but that usage is explained here.

https://www.sqlbi.com/articles/keepfilters-a-new-dax-feature-to-correctly-compute-over-arbitrary-sha...

[jdbuchanan71]

Ah, very nice.  Count on the Italians to have covered the topic!

[rodneyc8063]

Thanks @jdbuchanan71 and @AlexisOlson  - This is super helpful!

But also looks like I have quite a bit of homework this weekend to read over 😞