Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
chris_m
Helper I
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:

 

TypeSales OrderValue
CarrierSO0006170581.92581583
CustomerSO0006170585.27566944
Carrier1046402114.44512306
Customer1046402116.55827972
Confirmation104640214.27778E-05
Carrier11217215.31020194
Customer11217217.42505194
Carrier1047216117.11539333
Customer10472161-2.558227778
Confirmation104721612.86111E-05
Assignment1046923317.84224806
CarrierSO0006043915.72886111
Carrier1046622415.74705944
Carrier1046922615.76328528
Customer1046922617.8755
Confirmation104692263.16667E-05
Carrier1046923215.76449167
Customer1046923217.87687583
Confirmation104692323.33333E-05
AssignmentTOL9065417.88523889
Customer1046622417.90962222
Confirmation104662242.77778E-05
Carrier1046921815.79917306
Customer1046921817.91109917
Confirmation104692180.000025
CarrierPROGS4715.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

View solution in original post

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

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?

 

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:

 

C.png


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

Alberto Ferrari - SQLBI

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!

erik_tarnvik
Solution Specialist
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.

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
Solution Specialist
Solution Specialist

Hi @chris_m

 

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

 

Sales Order.png

 

 

 

I hope it helps

 

Regards

BILASolution

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

 

TypeSales OrderValue
CustomerSO0006170585.2757
Customer1046402116.5583
Customer11217217.4251
Customer1046922617.8755
Customer1046923217.8769
Customer1046622417.9096
Customer1046921817.9111

 

Then if I select these sales orders from the full list, I get

 

TypeSales OrderValue
CarrierSO0006170581.9258
CustomerSO0006170585.2757
Carrier1046402114.4451
Customer1046402116.5583
Confirmation104640210.0000
Carrier11217215.3102
Customer11217217.4251
Carrier1046622415.7471
Carrier1046922615.7633
Customer1046922617.8755
Confirmation104692260.0000
Carrier1046923215.7645
Customer1046923217.8769
Confirmation104692320.0000
Customer1046622417.9096
Confirmation104662240.0000
Carrier1046921815.7992
Customer1046921817.9111
Confirmation104692180.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

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
https://www.linkedin.com/in/excelenthusiasts/

hi @Ashish_Mathur

 

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

 

 

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @chris_m again.

 

Try this...

 

1.png

 2.png

 3.png

 

 

 

 

 

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

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

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.