Forum Discussion

PierreL69's avatar
PierreL69
Helper IV
9 months ago
Solved

Calculation groups & Column Hierarchy

Hi the community, Not sure if I posted in the right section. Anyway I opening this conversation beacuse I did not find any related topic through the research I did.   I have created a calculation...
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 months ago

    Hi PierreL69,

    I have 3 Approaches For you 😀❤️

     

    First One : Enable "Show Items with No Data"

    • Select your matrix visual

    • Go to Format pane → Values section

    • Turn ON "Show items with no data"

    • This often forces Calculation Groups to appear at all levels

    Second Approach: 

    • Instead of using the automatic date hierarchy, add columns individually, In your matrix columns field:
    Year
Quarter  
Month
Calculation Group
    • This creates a flat structure where the Calculation Group is always visible at every level.

    Third Approach : 

    • Modify your calculation items to handle different hierarchy levels (DAX):

    Sales Amount Calc = 
SWITCH(TRUE(),
    ISINSCOPE('Date'[Month]), [Sales Amount],
    ISINSCOPE('Date'[Quarter]), [Sales Amount],
    ISINSCOPE('Date'[Half]), [Sales Amount],
    [Sales Amount]
)

     

    Bonus Approach : 

    • Create a field parameter for your time hierarchy and use it alongside the Calculation Group in columns.
    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
PierreL69's avatar
PierreL69
Helper IV
9 months ago

Hi Ahmed-Elfeel & Thanks again.

I am struggling a little bit, so I have tried to test all the approaches you listed but nothing did the job.

I certainly did something wrong in one of the “calculations” you described. I am far away from being an expert.

However, for sure the grand total is activated and what I noticed is that the grand total is appearing when I am no longer at le lowest level of my column hierarchy, so in other word when I make my calculation group visible… but I go up in the hierarchy column with my date parameter (month / quarter / half / Year)  the grand total appear again.

May this behavior help you to point at which level my issue is and on which approach I should focus Second / Third / Fourth approach

It seems that my calculation group is making the grand total disappear

 

 

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    Hi PierreL69,

    The issue here is when calculation groups are applied at detailed levels Power BI struggles to aggregate the calculation items properly for grand totals.

     

    Each calculation item (like "Current", "Previous", "YTD") represents a different calculation context so Power BI does not know how to sum them up.

     

    Lets head over to Approach directly 😅❤️

    First One : Create Explicit Total Measures

    • Create dedicated measures that handle the total logic:

    Sales Amount Total = 
CALCULATE(
    [Sales Amount],
    REMOVEFILTERS('YourCalculationGroup'[CalculationItem])
)

-- Or for specific calculation items:
Sales Amount YTD Total = 
CALCULATE(
    [Sales Amount YTD],
    REMOVEFILTERS('YourCalculationGroup'[CalculationItem])
)

     

    Second Approach: Modify Your Calculation Items

    • Add a special case for total contexts:

    CALCULATION ITEM 'YourCG'[Total] = 
IF(
    ISINSCOPE('YourCalculationGroup'[CalculationItem]),
    [Your Regular Logic],
    CALCULATE([Your Base Measure], REMOVEFILTERS('YourCalculationGroup'[CalculationItem]))
)

     

    Third Approach : 

    • Use ISFILTERED to Detect Calculation Group Context
    Sales Amount With Total = 
IF(
    ISFILTERED('YourCalculationGroup'[CalculationItem]),
    [Your Calculation Logic],
    [Your Base Measure]
)

     

    Bonus Approach (Simplest)

    • Sometimes the simplest solution is to:

      • Keep your detailed matrix as is

      • Create a separate card or table visual showing the grand totals

      • Place it next to your matrix

    • Or create a separate field parameter that includes both your calculation items and a Total option.

    Tell me if this was useful and also if you encountered any issues feel free to ask ☺️❤️

     
     

     

     

     
     

     

     

    • PierreL69's avatar
      PierreL69
      Helper IV
      9 months ago

      Hi  Ahmed-Elfeel 

      I have tried everything without success,

      But a big thanks again for all your explanation.

      Maybe do you have a simple pbix file with calculation group + Parameter field (year/half/quarter/month) set in a matrix column visual showing me column grand total appearing at the lowest level of a matrix visual, and so with calculation group item appearing?

      Like this I could try to reproduce your logic ?

      Thanks again

    • PierreL69's avatar
      PierreL69
      Helper IV
      9 months ago

      Hi Ahmed-Elfeel 

      I have created another topic on the forum for my issue of column totals with Calculated groups & Parameter data field.

      I have also attached a PBIX file where people can show me if it is possible to make the column grand total appear when you are using parameter, and you are at the lowest of a column hierarchy with calculated groups

      So if you want to participate to the challenge and show me if it is possible  I will appreciate 

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        9 months ago

        Hi PierreL69 ,

        Thanks for the update. Since you’ve created a new thread for this issue, could you please close this one? It makes it easier for everyone to follow the discussion in one place.

        Thanks.