cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## 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 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

1 ACCEPTED SOLUTION

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

Alberto Ferrari - SQLBI
14 REPLIES 14

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

Alberto Ferrari - SQLBI
Helper I

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?

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

Alberto Ferrari - SQLBI
Helper I

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!

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.

Helper I

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

Solution Specialist

Hi @chris_m

I take your data sample and the picture below is the result.

I hope it helps

Regards

BILASolution

Helper I

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

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]))`

Regards,
Ashish Mathur
http://www.ashishmathur.com
Solution Specialist

Hi @chris_m again.

Try this...

1. Create 2 calculated columns as the pictures above.

2. Apply the filter as the last picture.

Value Average = AVERAGE(Table1[Value])

I hope it helps this time

Regards

BILASolution

Helper I

This isnt the most elegant solution, but it works!

After creating the additional columns, I used

`Average Test 2 = CALCULATE([Average],Data[Row to Use]="Yes") `

Where [Average] is

`Average = AVERAGEX(Data,Data[Value])`
Solution Specialist

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.