Calculation order - rows versus columns

[Andrew-HLP]

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 Map Acct Level 3'[Acct Level 3 Desc] = "Income")

Cost of Sales:
CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales")

Gross MArgin:
CALCULATE( SELECTEDMEASURE() , 
    KEEPFILTERS(
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
    'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales"  
    ) 
)

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

The measure in the var column is calculated as follows:

Sorry I originally posted the wrong line of code here

Var Actual CM Vs Actual-1 CM = [Actual CM] - [Actual-1 CM]  

The problem is the calculation in the rows takes precedence over variance calculation in the columns  - so the number circuled in red is not correct (it is based on the Gross Margin Var / Income Var rather than Last Year Gross Margin %c - This Mth Gross Margin %.

How can I fix this?

[v-ssriganesh]

Hi @Andrew-HLP,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Andrew-HLP]

Unfortunately not.  I have posted an update.

[Andrew-HLP]

Thanks for the reply.  I am a bit confused about what you are proposing here.  

The measures I currently have in the columns are called:

[Actual LM],   [Actual CM],   [Actual-1 CM], [Var Actual CM Vs Actual-1 CM],   [Var Actual CM Vs Actual-1 CM %]

They are renamed on the visual as:

The [Var Actual CM Vs Actual-1 CM] measure (i.e. the "VAR" column) works as required on the Income. Cost of Sales and Gross Margin lines.  It is only the Gross Margin % line (which is part of the calculation group) that I have a problem.

The measure in the var column is calculated as follows:

Sorry I originally posted the wrong line of code 

Var Actual CM Vs Actual-1 CM = [Actual CM] - [Actual-1 CM]  

You proposed changing the measure to:

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")

This generates an error as there is no measure called [Gross Margin %].  Could you please clarify?  

Also, can I just check that I can refer to the columns in a visual (generated by measures) in the way you have descroibed?  And if so, should I be using the full name of teh measure rather than the nicknames used in the visual?

P.S I did consider adding a calculated column to the visual but Power BI wont allow it - I assume that this is because calculated columns are not compatible with calculation groups (I can add them to other visuals)?

[v-ssriganesh]

Hi @Andrew-HLP,

Thanks for the feedback. I apologize for the confusion. The error occurred because [Gross Margin %] isn’t a standalone measure it’s in your calculation group. Since [Var Actual CM Vs Actual-1 CM] = [Actual CM] - [Actual-1 CM] works for other rows but not Gross Margin %, we need a targeted fix.

Use this new measure:

Var Adjusted =

IF(

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

    DIVIDE(

        CALCULATE([Actual CM], 'CG: Report Income Statement'[Income Statement] = "Gross Margin") -

        CALCULATE([Actual-1 CM], 'CG: Report Income Statement'[Income Statement] = "Gross Margin"),

        CALCULATE([Actual CM], 'CG: Report Income Statement'[Income Statement] = "Income"),

        0

    ) -

    DIVIDE(

        CALCULATE([Actual-1 CM], 'CG: Report Income Statement'[Income Statement] = "Gross Margin"),

        CALCULATE([Actual-1 CM], 'CG: Report Income Statement'[Income Statement] = "Income"),

        0

    ),

    [Var Actual CM Vs Actual-1 CM]

)

Replace [Var Actual CM Vs Actual-1 CM] in your matrix with [Var Adjusted]. This adjusts Gross Margin % variance to ~0.4% (93.2% - 92.8%) while keeping other rows intact.

• Yes, you can reference measures in DAX using their full names (e.g., [Var Actual CM Vs Actual-1 CM]) rather than the visual nicknames (e.g., "Var"). The nicknames are just display aliases and don’t affect the underlying DAX. Always use the measure’s original name in calculations for accuracy.

• You’re correct Power BI doesn’t allow calculated columns in visuals with calculation groups because calculation groups dynamically alter measure context at query time, which conflicts with static calculated columns. Calculated columns work in other visuals without calculation groups, as you noted.

I trust this information proves useful. If it does, kindly “Accept as solution” and give it a 'Kudos' to help others locate it easily.
Thank you.

[Andrew-HLP]

Thank you for this.  Unfortunately, I still cannot get this to work...

This is the code I am using for the measure (I corrected the 'Gross Margin %' calculation as you had an additional line).

The measure is called [Var Actual CM Vs Actual-1 CM PCT] but I used the alias 'Var Adj ' in the grid.

Var Actual CM Vs Actual-1 CM PCT = 

IF(

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

    DIVIDE(

        CALCULATE([Actual CM], 'CG: Report Income Statement'[Income Statement] = "Gross Margin"),

        CALCULATE([Actual CM], 'CG: Report Income Statement'[Income Statement] = "Income"),

        0

    ) -

    DIVIDE(

        CALCULATE([Actual-1 CM], 'CG: Report Income Statement'[Income Statement] = "Gross Margin"),

        CALCULATE([Actual-1 CM], 'CG: Report Income Statement'[Income Statement] = "Income"),

        0
    ),

    [Var Actual CM Vs Actual-1 CM]
)

STEP 1:

At first the original 'Var'  column and the new 'Var Adj' column were showing the same values.  I reasoned the IF statement at the top of the 'Var Adj' code wasn't working.

IF(SELECTEDVALUE('CG: Report Income Statement'[Income Statement]) = "Gross Margin %" ...

STEP 2: 

I created a new [Selected Line] measure and added an additional column to my matrix to show the selected value:

Selected Line = SELECTEDVALUE('CG: Report Income Statement'[Income Statement])

At first, [Selected Line] did not return a value on the 'Gross Margin %' line or any similarly coded line e.g. Overheads %, Net Profit %' etc.

STEP 3:

I looked at what was unique about these lines ,and determined it was that they refer to other lines within the calculation group (as opposed to the underlying data).

DIVIDE  

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

"-") 

STEP 4:

I experimented by modifying the 'Gross Margin %' formula within the calculation to refer to a non-existant line in the underlying data:

CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "XXXX")

and as expected the 'Gross Margin %' line returned the correct [Selected Line] name

STEP 5:

Having established the formula now returns the correct 'Selected Line' name I updated 'Gross Margin %'  formula within the calculation group as follows:

DIVIDE( CALCULATE( SELECTEDMEASURE() , 

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

CALCULATE( SELECTEDMEASURE() , 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income"), 

"-")

This doesn't work 

(i) The matrix errors and refuses to display any data when the [Selected Line] measure is on the grid.

(ii) If I remove it, the 'Var Adj' measure returns a blank, not than the correct value.

By making temporary changes to the code (forcing different values in to the <alternateresult> parameter of the DIVIDE function) I have been able to determine the issue is occurring because the 'Gross Margin %' formula in the calculation group is generating a divide by zero error (although the value shouldn't be / isn't zero!) and it works in all the other columns!

I have also been able to determine that if a enter a spurious formula for 'Gross Margin %' (e.g. to simply repeat the Gross Margin value) then whilst every other column shows the wrong value (i.e. the Gross Margin value rather than percent) the 'Var Adj' measure kicks in correctly and shows the right value (0.4%).  

Conclusion: There is something about the construction of the 'Gross Margin %' formula in the calculation group that is preventing the  'Var Adj' measure from working.  It is not the DIVIDE function itself (divide by a constant works OK) so I assume it is the multiple calculate statements (which I cant avoid).

NOTE TO STEP 3:

I had previously seen a similar problem with the calculation of the 'Margin' line when the calculation was based on summing the 'Income' and 'Cost of Sales lines from the calculation group, it also summed the percentages rather than calculating them afresh on the 'Gross Margin %' line.  I got around that problem by adjusting the 'Margin' calculation to refer to the underlying data rather than other lines within the calculation group:

CALCULATE( SELECTEDMEASURE() , 
KEEPFILTERS( 'GL Map Acct Level 3'[Acct Level 3 Desc] = "Income" || 
            'GL Map Acct Level 3'[Acct Level 3 Desc] = "Cost of Sales" )  
)

[v-ssriganesh · Accepted solution]

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.

[Andrew-HLP]

Thank you.  Will try this as soon as I am back from vacation.

[v-ssriganesh]

Hi @Andrew-HLP,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

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.