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

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!