Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
@ 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])))
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |