cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
amcoville Frequent Visitor
Frequent Visitor

DAX: Average a column but replace the column being averaged under a certain condition.

I have a measure that calculates an average like so:

 

CALCULATE ( AVERAGE( 'Budget’[FTE]), ‘Budget’[FTE] <> BLANK() )

 

The challenge that I have is that in one specific case, when a value in a different table = something I need to use a value other than FTE. Instead I need to use a calculated measure. Effectively, when the value in the other table is a specific value override [FTE] and use a measure instead. So in pseudo-code it needs to look something like this:

 

CALCULATE( AVERAGE( IF(‘Data’[Identifier] = ‘Foo’, [Measure], ‘Budget’[FTE] ), ‘Budget[FTE] <> BLANK() )

 

I hope this explains my issue, but let me know if you need any clarification.

Thanks so much in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX: Average a column but replace the column being averaged under a certain condition.

Depending on your relationships, you should be able to use RELATED to get the value.

 

If Data -> Budget is a oneSmiley Surprisedne or manySmiley Surprisedne relationship, you can use:

AvgWithReplace = AVERAGEX( 'Data', IF(‘Data’[Identifier] = ‘Foo’, [Measure], RELATED(‘Budget’[FTE])) )

 

If it is a many:many or one:many relationship, you need to use some sort of aggregation (like AVERAGE, or MAX) to let DAX know what value it should be using for 'Budget'[FTE]

5 REPLIES 5
Super User
Super User

Re: DAX: Average a column but replace the column being averaged under a certain condition.

You're very close with your pseudocode.  It's all about wrapping things in the right order.

 

 IF(‘Data’[Identifier] = ‘Foo’, [Measure], CALCULATE( AVERAGE(‘Budget’[FTE] ), ‘Budget[FTE] <> BLANK() ) )

This checks for Foo, if Foo condition is met, use the other measure, otherwise calculate the average of FTE where there are no blanks. 

Super User
Super User

Re: DAX: Average a column but replace the column being averaged under a certain condition.

After re-reading your main post, I realized that you may have had a different use case. 

 

If you don't want to use [Measure] instead of an average, but use it in the average where indicated, you can use AVERAGEX.  

 

AvgWithReplace = AVERAGEX( 'Data', IF(‘Data’[Identifier] = ‘Foo’, [Measure], ‘Budget’[FTE]) )

This will iterate over the table 'Data', and evaluate the IF statement for the value to use in the average for each row.  You may still need to wrap this in CALCULATE(..., 'Budget'[FTE]<>BLANK()) syntax if your total is coming out wrong and [Measure] isn't used to replace blanks.

amcoville Frequent Visitor
Frequent Visitor

Re: DAX: Average a column but replace the column being averaged under a certain condition.

Thank you @Cmcmahan. Yes, this response is what I am after, replacing the value to use in the average.

 

This gets me very close but there's some syntax I can't get quite right.

 

When I try to implement this:

 

AvgWithReplace = AVERAGEX( 'Data', IF(‘Data’[Identifier] = ‘Foo’, [Measure], ‘Budget’[FTE]) )

It does not like the last bit of 'Budget'[FTE]. It seems to only allow other measures there, I can't pick any columns. Is there some trick to use in this case?

amcoville Frequent Visitor
Frequent Visitor

Re: DAX: Average a column but replace the column being averaged under a certain condition.

OK, I see now that this is because the budget value is in a different table. These tables are related so I believe I can create a calculated column lookup to make this work. Out of curiosity is there another/better way?

Super User
Super User

Re: DAX: Average a column but replace the column being averaged under a certain condition.

Depending on your relationships, you should be able to use RELATED to get the value.

 

If Data -> Budget is a oneSmiley Surprisedne or manySmiley Surprisedne relationship, you can use:

AvgWithReplace = AVERAGEX( 'Data', IF(‘Data’[Identifier] = ‘Foo’, [Measure], RELATED(‘Budget’[FTE])) )

 

If it is a many:many or one:many relationship, you need to use some sort of aggregation (like AVERAGE, or MAX) to let DAX know what value it should be using for 'Budget'[FTE]

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 104 members 1,525 guests
Please welcome our newest community members: