Calculation order - rows versus columns

Hi Andrew-HLP,
Thank you for your update. It looks like the root cause is a combination of context transition issues and the DIVIDE function with multiple CALCULATE statements in your 'Gross Margin %' formula, which is causing a divide-by-zero error and preventing the SELECTEDVALUE function in your 'Var Adj' measure from working correctly.

To resolve this, I recommend modifying the 'Gross Margin %' formula in your calculation group to directly reference the underlying data and simplify the context. Based on your findings, here's an updated formula that should work:

DIVIDE(

    CALCULATE(

        [Actual CM],

        KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] IN {"Income", "Cost of Sales"})

    ) - CALCULATE(

        [Actual CM],

        KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales")

    ),

    CALCULATE(

        [Actual CM],

        KEEPFILTERS('GL Map Acct Level 3'[Acct Level 3 Desc] = "Income")

    ),

    0

)

This formula calculates the gross margin by subtracting 'Cost of Sales' from 'Income' and dividing by 'Income', using the underlying data directly. This should eliminate the divide-by-zero error and allow the 'Var Adj' measure to correctly display the variance (e.g., 0.4% as seen in your test case).

If this resolves it, feel free to “Accept as solution” and give it a 'Kudos' to help others.
Thank you.