Forum Discussion

PierreL69's avatar
PierreL69
Icon for Helper IV rankHelper IV
9 months ago

Calculation Group & Parameter in Column & Column Total

Hello everyone,
I decided to use calculated groups, which proved successful. Following this, I added a column parameter to dynamically manage time periods, which was also successful.

 

However, despite this success, I'm frustrated because I've lost the option to display the column totals when I'm at the last level of my column hierarchy and therefore when I display my calculated group, which must be displayed.

 

Is there any tricks to make the totals appear except the fact to duplicate the visual and keep one visual for total and one visual for playing ?

 

You can see here :

What I expect :

 

I have put my test PBIX file here :

Pbix file 

 

Thanks a lot

Kind regards,

Pierre.

 

 

 

 

 

 

 

5 Replies

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    9 months ago

    Hi PierreL69,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Here the issue is because when a Calculation Group is combined with a field or column parameter that switches between Year/Quarter/Month.
    When the matrix reaches the last level of the column hierarchy, the Total column no longer has a single time period in scope. In that situation the calculation item is evaluated in a multi-period context so it returns blank, which is why the totals disappear.

    You need to update the calculation items in Tabular Editor so that they return a correct value both at the leaf level and at the total level. Please follow below steps:

    • Detect whether a single time period is in scope like using ISINSCOPE( Year / Quarter / Month ). If one of them is in scope, return your normal calculation like Current, Previous, YoY, etc..
    • If none of them is in scope(which is the Total column), you must manually aggregate by iterating the visible periods:
      SUMX (VALUES( <The lowest grain column> ), <The shifted measure>

    This makes the calculation item produce a valid value when the field parameter removes the time grain from the total column.

    After updating each calculation item using this pattern, the current and previous totals will appear again exactly as expected without needing a duplicate visual or any workaround in the report.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • PierreL69's avatar
      PierreL69
      Icon for Helper IV rankHelper IV
      9 months ago

      Hi v-achippa 

      Thanks a lot for your reply,

      I already tried lot of solution listed there Related Topic by @Ahmed-Elfeel but unfortunately without success.

      That is why I created this topic with a simple PBIX file, so that people who know how to solve my problem can do so in this test file, and so that I can use it as a guide.

      Would you be able to display the Gand total Column in the file I attached to this thread ? 

      (If I don't have the subtotals per column, it's not a big deal).

      Thanks a lot

    • PierreL69's avatar
      PierreL69
      Icon for Helper IV rankHelper IV
      9 months ago

      hi v-achippa 

      I have tried again what you mentioned in my test file, but Total column still not showing up 

      Here the file with the updated calculation item :

      Updated PBIX file 

      I would really appreciate it if you could show me what's wrong and what the correct formula is.

      Thanks

       

       

       

       

      • v-achippa's avatar
        v-achippa
        Icon for Community Support rankCommunity Support
        9 months ago

        Hi PierreL69,

         

        Thank you for the response. Unfortunately, there is no native toggle to restore totals with dynamic columns yet. Since no other workarounds or solutions are working so I recommend submitting your detailed feedback and ideas through Microsoft's official feedback channels. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.

        Fabric Ideas - Microsoft Fabric Community

         

        Thanks and regards,

        Anjan Kumar Chippa