Variance and margin calculation

[Keropi79]

Hi,

I tried to do a visualization that looks like the below for the P&L:

Amount (USD)	Actual	Budget	Var
Revenue	60,000	50,000	16.67%
Service	50,000	35,000	30.00%
Others	10,000	15,000	-50.00%
Cost	48,500	37,000	-31.08%
Service	45,000	35,000	-28.57%
Others	3,500	2,000	-75.00%
Margin	11,500	13,000	-13.04%
Margin%	19%	26%	-6.83%

With pivot visualization, I manage to get the variance, however, I can't get to calculate Margin and Margin%. Is there any visualization that allows me to have calculation or metric by row?

Thanks.

[v-lionel-msft · Accepted solution]

Hi @Keropi79 ,

If you have such a fact table, you can use ± to mark income and expenses.

Then, you can create a calculated table.

Table = 
VAR Margin =  
ROW(
    "Amount (USD)", "Margin",
    "Actual", CALCULATE( SUM(Sheet5[Actual]), ALL(Sheet5) ),
    "Budget", CALCULATE( SUM(Sheet5[Budget]), ALL(Sheet5) ),
    "Var", BLANK()
)
RETURN
UNION(
    Sheet5,
    Margin
)

The same is true for the row ‘Margin%’. I don't know your mathematical calculation logic so I can't calculate it for you.

Best regards,
Lionel Chen

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

[amitchandak]

@ you have to create table like this and try

union (
summarize(table1, "Name", "Table1", "Value", sum(Table1[value])),
summarize(table2, "Name", "Table2", "Value", sum(Table2[value])),
summarize(table3, "Name", "Table3", "Value", sum(Table3[value]))
)

Union (
	Summarize(filter(Table,Sceniro="Budget"),Table[Business_category],"Name","Budget","Value",Sum(Table[Value])),
	Summarize(filter(Table,Sceniro="Actual"),Table[Business_category],"Name","Actual","Value",Sum(Table[Value])),
	Summarize(Table,Table[Business_category],"Name","Actual","Value",Sumx(Table,if(Sceniro="Actual",-1*Table[Value],1*Table[Value])))
	)