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,

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