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

erik_tarnvik's avatar
erik_tarnvik
Solution Specialist
8 years ago

Hi chris_m,

I assume [Total Value] is defined as Total Value = SUM(Table1[Value)? I don't have the definition of the measure [Average Value] so I am not sure exactly what is happening in your measure, but if you do this:

Average Value > 10 = 
AVERAGEX(
    FILTER(
           VALUES( Table1[Sales Order] ),
                   [Total Value] > 10 ),
    [Total Value] )

and then put Type in a table followed by [Average Value > 10], you will get a result that I think is accurate per type. However, the total for that table will not show what you expect. When constructing something like this, I feel it helps to break it up, so I would create the following measures:

NoOrders>10 = CALCULATE(COUNT(Table1[Sales Order]),Table1[Value]>10)

TotalValue>10 = CALCULATE(SUM(Table1[Value]),Table1[Value]>10)

Average>10 = DIVIDE([TotalValue>10],[NoOrders>10])

This will create the same results as the above, except that the Totals for the table will come out right. The difference has to do with context as you say. I am not sure I have understood your problem correctly but hope this helps.

  • chris_m's avatar
    chris_m
    Helper I
    8 years ago

    Hi erik_tarnvik

     

    Sorry, yes [Total Value] is a sum of the Value column. 

     

    Average Value is the AVERAGE function applied to the Value column. 

     

     

    I'll give your measures a try and see what I get. 

     

    Thanks