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.

v-lionel-msft's avatar
v-lionel-msft
Community Support
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.