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

AlbertoFerrari's avatar
AlbertoFerrari
Most Valuable Professional
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 AlbertoFerrari

 

I've tried your measure, but it only returns the average value for the "Customer" type. How can I make it calculate the average across all types for the filtered list of Sales Orders?

 

    • chris_m's avatar
      chris_m
      Helper I
      8 years ago

      Ah, I think I've figured it out. The example dataset I gave was quite simplified, and in my real dataset I was had grouped some of the 'Types' together, and was referencing the grouped type rather than the base types. 

       

      So if I use the base values in the measure, it works! But once the Groups get involved, in the measure or a visual, it stops working. 

       

      Thanks Alberto!