Forum Discussion
Helper ICalculate 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 average value for all types, but only for sales orders where the value for the 'Customer' type is greater than a certain amount.
This is what I've managed so far, but it calculates the average of all types where the value is greater than 10.
Other ways I have tried will only give me the average for the 'Customer' type.
Average Value > 10 =
AVERAGEX(
FILTER(
VALUES( Sales Order ),
[Total Value] > 10 ),
[Average Value] )Example of my data is like this:
| Type | Sales Order | Value |
| Carrier | SO00061705 | 81.92581583 |
| Customer | SO00061705 | 85.27566944 |
| Carrier | 10464021 | 14.44512306 |
| Customer | 10464021 | 16.55827972 |
| Confirmation | 10464021 | 4.27778E-05 |
| Carrier | 112172 | 15.31020194 |
| Customer | 112172 | 17.42505194 |
| Carrier | 10472161 | 17.11539333 |
| Customer | 10472161 | -2.558227778 |
| Confirmation | 10472161 | 2.86111E-05 |
| Assignment | 10469233 | 17.84224806 |
| Carrier | SO00060439 | 15.72886111 |
| Carrier | 10466224 | 15.74705944 |
| Carrier | 10469226 | 15.76328528 |
| Customer | 10469226 | 17.8755 |
| Confirmation | 10469226 | 3.16667E-05 |
| Carrier | 10469232 | 15.76449167 |
| Customer | 10469232 | 17.87687583 |
| Confirmation | 10469232 | 3.33333E-05 |
| Assignment | TOL90654 | 17.88523889 |
| Customer | 10466224 | 17.90962222 |
| Confirmation | 10466224 | 2.77778E-05 |
| Carrier | 10469218 | 15.79917306 |
| Customer | 10469218 | 17.91109917 |
| Confirmation | 10469218 | 0.000025 |
| Carrier | PROGS47 | 15.82534444 |
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
14 Replies
- AlbertoFerrari
Most Valuable Professional
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_mI'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?
- AlbertoFerrari
That looks strange, please check the file here:
https://www.dropbox.com/s/r0req2vawl8odip/Chris_M%201.pbix?dl=0
I see the average for all the types:
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
- erik_tarnvik
Solution Specialist
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
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
- BILASolution
Hi chris_m
I take your data sample and the picture below is the result.
I hope it helps
Regards
BILASolution
- chris_m
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
Type Sales Order Value Customer SO00061705 85.2757 Customer 10464021 16.5583 Customer 112172 17.4251 Customer 10469226 17.8755 Customer 10469232 17.8769 Customer 10466224 17.9096 Customer 10469218 17.9111 Then if I select these sales orders from the full list, I get
Type Sales Order Value Carrier SO00061705 81.9258 Customer SO00061705 85.2757 Carrier 10464021 14.4451 Customer 10464021 16.5583 Confirmation 10464021 0.0000 Carrier 112172 15.3102 Customer 112172 17.4251 Carrier 10466224 15.7471 Carrier 10469226 15.7633 Customer 10469226 17.8755 Confirmation 10469226 0.0000 Carrier 10469232 15.7645 Customer 10469232 17.8769 Confirmation 10469232 0.0000 Customer 10466224 17.9096 Confirmation 10466224 0.0000 Carrier 10469218 15.7992 Customer 10469218 17.9111 Confirmation 10469218 0.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
- erik_tarnvik
Oh. Well, my solution will not accomplish that. But it can for sure be done. It’s late here, I’ll check back in tomorrow night and if you don’t have a solution by then I’ll take another crack at it.
