cancel
Showing results for
Did you mean:
Helper I

## 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.

1 ACCEPTED SOLUTION
Super User

@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])

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
3 REPLIES 3
Helper I

@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.

Super User

@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])

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Helper I

Thank you so much @amitchandak. Worked like a charm.

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!