Calculation groups & Column Hierarchy

[PierreL69]

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

[PierreL69]

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]

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]

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]

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.

[PierreL69]

But how can I closed it, as I already accepted a solution in this topic ?

[v-sshirivolu]

Hi @PierreL69 ,
Once a solution is accepted, the thread is already considered closed. Thanks for confirming.

[PierreL69]

I thought it was kind of clossed already because I accepted the solution for my first issue.

Sorry, I will close it thanks.

[PierreL69]

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

[Ahmed-Elfeel · Accepted solution]

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]

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]

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☺️❤️