Forum Discussion
Variance between two measure values
Hi All, I am attempting to calculate the difference between multiple forecast scenarios. As an example, from the screeshot below, i am attempting to calculate the difference column based on the prior forecast.
Forecast period CY
| Forecast | Value | Difference | |
| Forecast 1 | 28.2 | ||
| Forecast 2 | 12.9 | -15.3 | |
| Forecast 3 | 28.7 | 15.8 |
In attempting to calulate the difference I have used the following dax, but I receive the same "value".
Value - calculate(Value,selectedvalue(forecast)="forecast 1")
This returns only the forecast 2 value as the forecast 1 value returns as 0.
Any assistance would be greatly appreciated.
jawilson808 , A new column
new column =
[Value] - Sumx(filter(Table,[forecast]="forecast 1"),[value])/// or a new measure
new measure =
sum(Table[Value]) - Maxx(filter(allselected(Table),[forecast]="forecast 1"),[value])
3 Replies
jawilson808 , A new column
new column =
[Value] - Sumx(filter(Table,[forecast]="forecast 1"),[value])/// or a new measure
new measure =
sum(Table[Value]) - Maxx(filter(allselected(Table),[forecast]="forecast 1"),[value])Thank you so much amitchandak. Worked like a charm.
amitchandak A follow up question to the above. How can I critique this formula for when its drilled down on. As an example, when drilling down on Forecast 2 the difference is equal to it self. I believe this has something thing to do with the "maxx" used in the formula.
