Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
6 years ago

Average measure in Pivot Table with PQ

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]))

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

12 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

      • az38's avatar
        az38
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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

         

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      cristianml
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      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's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Hello

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

        Good luck

        Jimmy