Forum Discussion

Andrew-HLP's avatar
Andrew-HLP
Icon for Helper I rankHelper I
1 year ago
Solved

Calculation order - rows versus columns

Below is an extract from a matrix visual which is comprised of measures in the columns and a calculation group in the rows     Calculation Group: Sales: CALCULATE( SELECTEDMEASURE() , 'GL ...
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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.

v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
1 year ago

Hi Andrew-HLP,
Thank you for reaching out to the Microsoft Fabric Forum Community.

After thoroughly reviewing the details you provided,, you can create a new measure to calculate the variance directly based on Gross Margin % values.

Var Gross Margin % =

CALCULATE([Gross Margin %], 'CG: Report Income Statement'[Income Statement] = "This Mth") -

CALCULATE([Gross Margin %], 'CG: Report Income Statement'[Income Statement] = "Last Yr")

Replace the existing "Var" measure in the column section of your matrix visual with this new measure.

  • Check your calculation group definition for Gross Margin % remains correct.

This should yield a variance of ~0.4%, aligning with your expectation. Test this in your model, and if your data includes dynamic filters or slicers, verify the results accordingly. If you encounter issues, double-check your calculation group and data model context, or let us know for further assistance.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.