Forum Discussion

chris_m's avatar
chris_m
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper I
8 years ago

HI BILASolution

 

I want to calculate the average value across all types, but only for the sales orders where the 'Customer' type has a value >10

 

So if I manually filter in excel, I get the following sales orders that have a value >10

 

TypeSales OrderValue
CustomerSO0006170585.2757
Customer1046402116.5583
Customer11217217.4251
Customer1046922617.8755
Customer1046923217.8769
Customer1046622417.9096
Customer1046921817.9111

 

Then if I select these sales orders from the full list, I get

 

TypeSales OrderValue
CarrierSO0006170581.9258
CustomerSO0006170585.2757
Carrier1046402114.4451
Customer1046402116.5583
Confirmation104640210.0000
Carrier11217215.3102
Customer11217217.4251
Carrier1046622415.7471
Carrier1046922615.7633
Customer1046922617.8755
Confirmation104692260.0000
Carrier1046923215.7645
Customer1046923217.8769
Confirmation104692320.0000
Customer1046622417.9096
Confirmation104662240.0000
Carrier1046921815.7992
Customer1046921817.9111
Confirmation104692180.0000

 

from here I want to calculate the average value of each of the types, which I think would be:

Carrier 24.9650

Confirmation 0.0000

Customer 27.2617

 

 

Thanks

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi chris_m,

 

Try this calculated field formula

 

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

Hope this helps.

  • chris_m's avatar
    chris_m
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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]))