Average Dax

[cristianml]

Hi,

The following measure is not showing the correct Average in the Pivot Table. It Should be 86 instead of 93:

Rate Card Value=IF([Actual LCR]=BLANK(), BLANK(),AVERAGEX(Rate_Card_Table, Rate_Card_Table[Rate Card Value]))

Follow the measure: 

Actual LCR =CALCULATE(AVERAGE(RT_Actual[Quantity]),RT_Actual[Category]="Cost Rate")

The measure Rate Card Value shows 93 because is the average of the table "Rate_Card_Table" by "Level". But in the pivot table I have different quantity of "Level" because it depends of another table that is "RT_Actual".

How can I modify the mesure Rate Card Value so I see ONLY the calculation of the visible or avaible information in the pivot table that in this case is 86 ?

Thanks!

[Jimmy801 · Accepted solution]

Hello @cristianml 

I think this post already exists. We tried already to help, but where not able.

A measure in a pivot table will be filtered on row level (in your row "Irland" and "Senior management"). But if we are coming to a grand-total it's simple no filter applied. This means you are not getting the average of the averages calculated in the upper row levels, but the overall average of your data, witch its all different.

In this scenario you can use 2 different measures on row level and grandtotal. To do the average of all averages you may only be able to in a static way using SUMMARIZE and ADDCOLUMNS. But maybe somebody else is able to help

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[artemus]

Just replace your AVERAGEX(...) with a CALCULATE(AVERAGEX(...), ALL(TABLE_WITH_LEVEL_COLUMN))

Actual results depend on exactly how your model is set up.