Forum Discussion
Using Measure in Calculation Not Returning Expected Results.
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] ) - 12500Second formula edited thanks to CNENFRNL catching my copy and paste error!
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!
Greg_Deckler I'm afraid there was a slip of pen in
[THRESHOLD] = AVERAGEX ( 'Table',[INVOICE AMOUNT] ) - 12500instead, I think it's
[THRESHOLD] = AVERAGEX ( ALL ( 'Table' ), [INVOICE AMOUNT] ) - 12500CNENFRNL - 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.
