Variance Reporting

[JadenWelch]

I have a Power BI matrix where I have the field "Scenario" as my Columns, the field "Industry" as my Rows, and two Values, Revenue & Expense, which are both calculated metrics. However, I have enabled the feature to switch values to rows.

The "Scenario" field has 2 values: "Current Forecast" and "Current Budget".

Currently, the end result of the matrix visually looks like this: It has Two columns called Current Forecast & Current Budget. It lists all the industries as Rows, and under each industry, are two rows for Revenue and Delivery Expense related to that Industry.

I want to adjust my matrix to be able to visually have a third column showing Variance to Budget, where it subtracts the Scenario field from each other (Current Budget from the Current Forecast). I want to leave my existing values as rows.

My end result should visually look like this:

It has Three columns called Current Forecast & Current Budget & Variance to Budget. It lists all the industries as Rows, and under each industry, are two rows for Revenue and Delivery Expense related to that Industry.

[Anonymous]

Hi @JadenWelch 

Could you please provide me with some sample data and output based on your sample data? That would be very helpful. Please remove any sensitive data in advance. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu

[JadenWelch]

@Anonymous 

Attached is a picture of sample data, along with what it currently looks like. I've also shown an image from excel of what I'm trying to get it to do. 

[Anonymous · Accepted solution]

Hi @JadenWelch 

After my testing, the following methods can achieve the effect you want for your reference:

Create several measures as follow

CurrentBudget = CALCULATE(SUM('Table'[Amount]), FILTER('Table', [Scenario] = "Current Budget"))

CurrentForecast = CALCULATE(SUM('Table'[Amount]), FILTER('Table', [Scenario] = "Current Forecast"))

VB = [CurrentForecast] - [CurrentBudget]

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.