Budget to Actual in a matrix

[Anonymous]

Hi,

I have been trying to add in a variance column to better shows the difference from Budget-Actuals. Originally i tried to create a new column in the table (one table houses both actuals and budgets, i merged the budgets onto that actual table) and that didnt work because it was summing all there vaiances together, giving me and incorrect value. Would i need to create a measure to bring into this matrix, i am fairly new to this BI and havent used them so much. I would like to have a variance column for each location. The "Group" is a combination off account codes that role up into those groups. The groups are stored in a different table and then i create a relationship to match that with the actuals (Which are broken down by each transaction that hits an account with a certain location, department...ect).

Here is what the matrix looks like.

Any suggestions would be greatly appeciated, thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

I would suggest you establish relationship from Location table to Budget Table and from Location table to Actual table. Then, drag location field from Location table into "Rows" of Matrix. Add below measures into "Values" of Matrix.

Budget=SUM(BudgetTable[Budget])

Actual=SUM(ActualTable[Actual])

Difference=[Budget]-[Actual]

For more advice, please provide sample data of above three tables and show us desired output.

Best regards,

Yuliana Gu