Using Measure in Calculation Not Returning Expected Results.

[TD21]

I have a measure called THRESHOLD, where [THRESHOLD] = AVERAGE ( [INVOICE AMOUNT] ) - 12500. The currently value of THRESHOLD is 6000.

If I create a column called TEST1, where TEST1 = IF ( [INVOICE AMOUNT] < 6000 , "YES" , "NO" ). There are 25 records with "YES" values, which is the correct count.

However, if I create a column called TEST2, where TEST2 = IF ( [INVOICE AMOUNT] < [THRESHOLD] , "YES" , "NO" ), I get 0 record with "YES".

Any idea why TEST2 isn't working as intended?

[Greg_Deckler · Accepted solution]

@TD21 - It is generally a bad idea to use a measure in a calculated column. There are 2 reasons why

1. Row context

2. Calculated columns are not dynamic

Now, in your case the Row context is the problem. Let's see, what is the AVERAGE of a singe row in your INVOICE AMOUNT table? Hmm, that would be the value for [INVOICE AMOUNT] in that row. The AVERAGE of a single value is the value. Hence, that row's [INVOICE AMOUNT] can never be less than itself and definitely won't be less than itself minus some number and hence why you get NO for everything.

Now, in your case, if you are not worried about 2, then you could get around this by changing your THRESHOLD formula to:

[THRESHOLD] = CALCULATE(AVERAGE ( [INVOICE AMOUNT] ),ALL('Table')) - 12500

or

[THRESHOLD] = AVERAGEX ( ALL('Table'),[INVOICE AMOUNT] ) - 12500

Second formula edited thanks to @CNENFRNL catching my copy and paste error!

[CNENFRNL]

@Greg_Deckler I'm afraid there was a slip of pen in

[THRESHOLD] = AVERAGEX ( 'Table',[INVOICE AMOUNT] ) - 12500

instead, I think it's

[THRESHOLD] = AVERAGEX ( ALL ( 'Table' ), [INVOICE AMOUNT] ) - 12500

[Greg_Deckler]

@CNENFRNL - Yeah, sorry, I was messing around and I copied my Column formula version over. When you are in a calculated column and refer to a table or a column within an aggregation you actually are not in row context. You are in the context of all rows. This is why Column = MAX([Value]) returns the maximum of the entire column (highest row). But, if you use a measure and refer to a column then the row context becomes part of the overall filter context and you return only the value of the current row in your aggregation. Nifty huh? So, this is why it is almost always a bad idea to use measues in calculated columns because they operate very differently than what one probably expects. So, yes, the measure version of that would need the ALL('Table').

Since it was marked as the solution, I'm going to edit it so people that don't read the entire thread don't get confused.

I attached my working copy PBIX below sig, Table (4) if you want to see what I was messing around with.