Forum Discussion

chris_m's avatar
chris_m
Helper I
8 years ago
Solved

Calculate Average based on filtered list

Hi   I've been struggling with a measure for a while and just can't seem to crack it. I feel like I am close but can't quite manage to unfilter in the right context.    I want to calculate the av...
Need Help
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    Here you are:

     

    M =
CALCULATE (
    AVERAGE ( Data[Value] ),
    CALCULATETABLE (
        VALUES ( Data[Sales Order] ),
        Data[Type] = "Customer",
        Data[Value] >= 10
    )
)

     

    CALCULATETABLE finds the Sales Orders who are Customer with Value greater than 10, then you use those Sales Order to filter the table.

     

    I know... DAX is an amazing language. When you see the solution you think: "yes, it is obvious", when you need to write it, you struggle in finding the right way. It only takes time and patience, thinking in DAX comes after some time :)

     

    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

chris_m's avatar
chris_m
Helper I
8 years ago

hi Ashish_Mathur

 

This one seems to work the same as the previous filter measures - it doesn't select only the sales orders where the customer value is >10

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi chris_m,

 

It works fine for me.  Please see the screenshot.  I just slightly modified the formula to also show the value of 0.  The revised formula is

 

=if(ISBLANK(AVERAGEX(FILTER(SUMMARIZE(Data,Data[Sales Order],"ABCD",SUM(Data[Value])),[ABCD]>10),[ABCD])),0,AVERAGEX(FILTER(SUMMARIZE(Data,Data[Sales Order],"ABCD",SUM(Data[Value])),[ABCD]>10),[ABCD]))