Power BI & DAX Tutorial: How to Implement Allocations

If you are in FP&A and trying to understand profitability of your products/business units, you have had to face a challenge of allocating certain corporate/HQ expenses. In this tutorial I go over the steps of implementing allocations in Power BI:

Desktop file location: https://bit.ly/3dOUTNV

The calculations below are used in the tutorial:

Rent =
CALCULATE ( SUM ( GL[Amount] ), KEEPFILTERS ( GL[Account] = “Rent” ) )

Amount with Allocation =
VAR currentTimePeriod = GL[Date]
VAR currentProduct = GL[Product]
VAR currentDivision = GL[Division]
VAR expenseToAllocate =
    CALCULATE ( [Rent], FILTER ( ALL ( GL ), GL[Date] = currentTimePeriod ) )
VAR salesInCurrentPeriod =
    CALCULATE (
        SUM ( GL[Amount] ),
        FILTER ( ALL ( GL ), GL[Date] = currentTimePeriod && GL[Account] = “Sales” )
    )
VAR salesInCurrentPeriodDivisionProduct =
    CALCULATE (
        SUM ( GL[Amount] ),
        FILTER (
            ALL ( GL ),
            GL[Date] = currentTimePeriod
                && GL[Division] = currentDivision
                && GL[Product] = currentProduct
                && GL[Account] = “Sales”
        )
    )
RETURN
    SWITCH (
        GL[Account],
        “Rent”, BLANK (),
        “Sales”, GL[Amount],
        GL[Amount]
            + expenseToAllocate
                * DIVIDE ( salesInCurrentPeriodDivisionProduct, salesInCurrentPeriod )
    )

Flow Amount =
IF (
SELECTEDVALUE ( Flow[Flow] ) = “Original”,
SUM ( GL[Amount] ),
SUM ( GL[Amount with Allocation] )
)

Expense =
CALCULATE ( [Flow Amount], KEEPFILTERS ( GL[Account] <> “Sales” ) )

Sales =
CALCULATE ( [Flow Amount], KEEPFILTERS ( GL[Account] = “Sales” ) )

Margin =
[Sales] – [Expense]

Margin % =
DIVIDE ( [Margin], [Sales] )