cancel
Showing results for
Search instead for
Did you mean:
Highlighted
amcoville 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

## 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 one ne or many ne 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

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

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

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

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

## 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 one ne or many ne 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
Top Ideas Top Kudoed Authors
Users Online
Currently online: 104 members 1,525 guests
Recent signins:
• KennethWang • benP • vichus • gritman • neldarov • rtrohitsingh • katerynasayenko Please welcome our newest community members:
• philpunter • thaoha • eebustamante • CVianello • andrewpp • barrychen0825 • kip88 