Dax Average calculation - data filtered in Pivot Table

[cristianml]

Hi,

I have an issue with one of my measures that is not showing the correct Average in the Pivot Table. If you see in the screenshot below, the measure Rate Card Value should show 86 instead of 97. I need to modify this measure so It can show the correct average that is 86 in the Total of column of the Pivot Table :

This is the measure that I need to MODIFY so it can show the correct average. But at the same time I need to Keep "Blank()" if in the other table where comes the measure "Actual LCR" is blank too.

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

Any ideas,? Thanks!

[az38]

hi @cristianml 

without full data model I could only suggest 2 p=issues:

first, correct compare with blank as

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

second, replace blank() to 0

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

do not hesitate to give a kudo to useful posts and mark solutions as solution

[cristianml]

Hi @az38 ,

Is not working. Also find below the data Model: 

[az38]

@cristianml 

Measures_RT_Actual is disconnected from other data models

whats statement defines [Actual LCR] measure?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[cristianml]

Hi @az38 ,

Follow the statement for Actual LCR:

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

I have a measure table to have ONLY measures there. But as you see the "Actual LCR" comes from te table RT_Actual.

Hope this helps

[v-juanli-msft]

Hi @cristianml 

You could change the measure as below

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

You could refer to some similar threads:

Incorrect measure total

Dealing with measure total

We can't reproduce your problem, could you share a simple example file without your privacy information?

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cristianml]

Hi @v-juanli-msft ,

I couldn't find the icon to attach the PBIX file. 
To understand better the problem see the following screenshot:

The main issue is due to the quantity of Levels. With the measure you shared of Averagex it shows 93 and this is because the total average of the Rate card table is 93 that has fixed Levels that in this case are 9:  (Asoc Director, Manager, Analyst, etc) BUT in the Pivot Table we have different quantity and this depends on other table where comes the measure "Actual LCR" and a particular Month/Period.  If you see in the screenshot the Correct Average should be 86.  I don´t know how change the measure showing the correct average based on the levels Availables or filtered in the pivot table instead of showing the average of the Rate Card table. 

I know that should be some trick to get this correct average of 86 but not sure how.

Thanks.