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

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 onene or manyne 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.

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.

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?

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 onene or manyne 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]

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 104 members 1,525 guests
Recent signins: