Forum Discussion

Keropi79's avatar
Keropi79
Frequent Visitor
6 years ago
Solved

Variance and margin calculation

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,0...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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's avatar
amitchandak
Super User
6 years ago

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