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

[Anonymous]

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!

[Cmcmahan]

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. 

[Cmcmahan]

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.

[Anonymous]

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?

[Anonymous]

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?

[Cmcmahan · Accepted solution]

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

If Data -> Budget is a one:one or many:one 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]