Average measure in Pivot Table with PQ

[cristianml]

Hi,

I have an issue with a meaure that shows the average but not the one I Expected. If you see in the screenshot below it shows the average 93 but should be 86. This is because is taken the average from the table from where has all the lines but I need to see in the pivot table the average ONLY from the lines that has data.

I'm using this meaure to get the average from the table but is also considering all values :

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

[Jimmy801]

Hello @cristianml 

you can use this measure here

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

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[cristianml]

Hi @Jimmy801 ,

I tried it but is not working:  Now is worse: Showing an average of 97  and should be 86 as you see in the screenshot. 

Note: The measure Actual LCR  comes from another table:

[Jimmy801]

Hello

Then you should put the correct table name in front of the column and surround it with the releated-function.

Good luck

Jimmy

[az38]

hi @cristianml 

try a simple measure

Measure = CALCULATE(
AVERAGE(Rate_Card_Table[Rate Card Value]);
NOT(ISBLANK([Actual LCR]))
)

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

[cristianml]

Hi @az38 ,

Is not working: 

[az38]

@cristianml 

what field do you want to aggregate? Rate Card Value or Actual LCR ?

Measure = CALCULATE(
AVERAGE(Rate_Card_Table[Actual LCR]);
NOT(ISBLANK([Actual LCR]))
)

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

[cristianml]

Hi @az38 ,

I want to modify the measure Rate Card Value in order to calculate the correct average.

The Rate Card Value comes from Rate card table and the Actual LCR comes from RT Actual Table. 

Thanks!

[az38]

@cristianml 

So, I as see on the screenshot you try to create a measure called "Rate Card Value" to create average of Rate Card Value?

Its impossible, try to rename your measure

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

[cristianml]

Hi @az38 ,

The measure Rate card value is already created but I need to modify this in order to calculate correctly the measure.

This is current measure for Rate card value 

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

But I need to modify this to calculate the average correctly. If helps I need to see the average of the filtered information only, not the average of All. So not sure if the following formulas could help on this situation : if selectedvalue or if filter 

or perhaps we can use the funtion VAR / RETURN 

Thanks!

[Jimmy801]

Hello @cristianml 

I gave the averagex-formula a test and it seems to work nicely

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[cristianml]

Hi @Jimmy801 ,

I tried to add "RELATED"  but  I couldn't. 

The table from where comes the Measure Actual LCR  comes from a table that is not a Column, is a category. LCR means Cost rate (marked in yellow in the table) : See the screenshot:

So how can I modify the RELATED in this case so can work properly ?

Thanks.

[Jimmy801]

Hello @cristianml 

sorry, but I can't follow anymore. I don't know the relationship nor do I understand your datamodel. As shown in my examples the measure I proposed is working (to check another column if it's empty or not equal 0. Now you tell me that is category next.

I'm very sorry, but I'm not able to help anymore

Jimmy