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