Need help to calculate the variance

[RichOB]

Hi, 

I have merged a budget and balance report but I now need need to show the variance in a column next to the budget.

Here's how the page looks:

Here's a snapshot of the worksheet columns. The balance and budget are in the same column for merging purposes. Not sure if that matters. There are 25 different categories not just the "Contract Income" above:

 

Thanks in advance!

[RichOB]

thanks for your help, here's the table format:

[MNedix]

You attached screenshots again. Use the below:

[RichOB]

Ah sorry here you go

Scheme Name	Category	Amount	Quarter	Type
Bridgend Stepdown	Contract Income	3053.22	Q1	Budget
Bridgend Stepdown	Contract Income	3053.22	Q1	Budget
Bridgend Stepdown	Contract Income	3053.22	Q1	Budget
Bridgend Stepdown	Contract Income	3053.22	Q2	Budget
Bridgend Stepdown	Contract Income	3053.22	Q2	Budget
Bridgend Stepdown	Contract Income	3053.22	Q2	Budget
Bridgend Stepdown	Contract Income	3053.22	Q3	Budget
Bridgend Stepdown	Contract Income	3053.22	Q3	Budget
Bridgend Stepdown	Contract Income	3053.22	Q3	Budget

Bridgend Stepdown	Contract Income	9159.68	Q1	Balance
Bridgend Stepdown	Contract Income	3053.23	Q2	Balance
Bridgend Stepdown	Contract Income	3052.23	Q2	Balance
Bridgend Stepdown	Contract Income	3953.23	Q2	Balance
Bridgend Stepdown	Contract Income	4610	Q2	Balance
Bridgend Stepdown	Contract Income	4610	Q2	Balance
Bridgend Stepdown	Contract Income	4610	Q3	Balance

[MNedix · Accepted solution]

Is this what you were looking for? You can simply create a measure for Balance and another one for Budget and add them to the matrix table.

If this is the solution then please mark it accordingly.

[RichOB]

This worked perfectly, thanks for your patience and help! Rich

[MNedix]

Hi,

Variance of what-against-what? Balance-Budget or Balance Q1-Balance Q2 or something else? Do you have measures behind Balance and Budget or are they just labels? 

[RichOB]

Hi @MNedix 

It would be the balance figure minus the budget figure. There aren't any measures behind the balance and budget, I just named them that to differentiate what they are.

Thanks

[MNedix]

Then a simple measure could probably do the job (replace 'Table' with your table):

Variance = 
VAR _balance = CALCULATE(SUM('Table'[Amount]),'Table'[Type]="Balance")
VAR _budget = CALCULATE(SUM('Table'[Amount]),'Table'[Type]="Budget")
RETURN
_balance - _budget

Then add the measure to the table visual. But keep in mind that this assumes that in Column Type you have both Balance and Budget (at least this is what I understood from your screeshot).

If this solved your problem pleae mark it as the solution.

[RichOB]

Hi @MNedix thanks for your reply. I've got the variance in the table now but it's coming up twice. Any ideas how I can change this to show just one on the far right column please?

[MNedix]

I don't really understand where the Amount and Variance Amount are coming from in the Values of your matrix. Can you paste your initial data in table format so I can try replicating your problem (you only put a screenshot)?