Calculate Average based on filtered list

[chris_m]

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

[AlbertoFerrari · 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

[chris_m]

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?

[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

[chris_m]

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]

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

[Ashish_Mathur]

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.

[chris_m]

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

[Ashish_Mathur]

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

[BILASolution]

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

[chris_m]

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

[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.