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 groups which work perfectly fine, I have a matrix visual with several columns or level.

When I go to the lowest level of data everything is working fine, but when I decide to go up in the column hierachy I am loosing my calculation group

 

So here my data in the column + calculation group (red)

 

And below my matrix visual setup at the lowest level in the column hierarchy with my calculation group visible (green)

but if I go up just one level I am lossing my calculation group :

 

Is there a way to keep the calulation group at every level of the column hierarchy ?

like the below screenshot but to do that I had to delete the month level in the column hierarchy

 

 

It could be a good things to have an option to keep always visible the calculation group at the lowest level to have it always visibile when you go up in the column level

 

thanks for your helps

 

Thanks

 

 

 

  • 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.

11 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    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 a lot for the time you passed to answer me,

      So, I tested 3 of the 4 solutions you proposed and the one which worked along the 3 was the bonus approach. I did not test yet the third approch as I found my solution with your last/bonus approach.

       

      I have still one thing missing it is the column grand total, I cannot make it appear, and the bonus would be to have the columns sub totals (but less needed)

      Do you have any idea how to make the column grand total appear ?

      Thanks a lot for your helps

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

        Hi PierreL69,

        First let me tell you

        Why Grand Totals Might Not Appear?

        • When you use Field Parameters and Calculation Groups in the columns of a matrix, the Grand Total for columns might not display because:
          • The way Calculation Groups are evaluated in the context of Field Parameters can sometimes suppress the grand total.
          • The matrix visual might not know how to aggregate the Calculation Items across the Field Parameter selections.

        So How to Fix this?

        Here is 2 or 4 approaches ☺️

         

        First One : Enable Column Grand Totals in Formatting

        1. Select your matrix visual

        2. Go to Format pane → Totals section

        3. Turn ON Column grand total

        4. Also check Row grand total if needed

        Second Approach: Check Your DAX

        • Try modifying your base measure
        Sales Amount = 
IF(
    ISFILTERED('YourCalculationGroup'[CalculationItem]),
    [Your Original Measure],
    SUMX(
        SUMMARIZE(
            'YourFactTable',
            'Date'[Year],
            'Date'[Quarter],
            "Total", [Your Original Measure]
        ),
        [Total]
    )
)

         

        Third Approach : Force Totals with HASONEVALUE

        • Modify your calculation items to explicitly handle total contexts :
        CALCULATION ITEM 'YourCG'[YourItem] = 
IF(
    HASONEVALUE('Date'[Month]) || HASONEVALUE('Date'[Quarter]),
    [Your Logic],
    SUMX(VALUES('Date'[Year]), [Your Logic])
)

         

        Fourth Approach : Field Parameters Config

        Make sure your field parameter is set up correctly :

        • The field parameter should include all hierarchy levels

        • Use the field parameter in columns then add calculation group

         

        I hope this is useful if you have any other questions let me Know☺️❤️

  • 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