Forum Discussion

DuncanP's avatar
DuncanP
Icon for Advocate II rankAdvocate II
4 years ago

Power BI Calculation Group subtotals

Hi

 

I'm using calculation groups in Power BI Desktop, but I've found out that subtotals are not displayed (which does make sense as you wouldn't want to add together Current and PY totals for example).

The specific issue I have is that I want a matrix that displays months on the rows, and then categories and a time intelligence calculation group on the columns. I then want to expand the columns at all levels so that you have something like this:

 

MonthCategory A Category B Total 
 CurrentPYCurrentPYCurrentPY
Jan34535685781212021169
Feb39531383774212321055
Mar46738785974913261136
Apr36434679476411581110

 

The problem is that in Power BI Desktop, I can't get the last two columns to display. Does anyone know if it's possible, or if there's a workaround somehow?

 

Duncan

Need Help

7 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi DuncanP 

     

    If you are using a Matrix visual, it is easy. In Format pane, under Subtotals, turn on Column subtotals.

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • DuncanP's avatar
      DuncanP
      Icon for Advocate II rankAdvocate II
      4 years ago

      Hi v-jingzhang 

      Thank you for replying, but unfortunately that doesn't work for calculation groups, and in fact the option is missing altogether!

      In my example, Current and PY are two calculation items in a calculation group.

       

      Duncan

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Did you solve this?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DuncanP,

     

    Have you found a solution by any chance? I am running into the same problem. Calculation Groups work fine, but as soon as I put them in a Matrix as a second column, the totals disappear. Which as you stated makes sense for the individual measures. But for the categories (first column) it does make sense to show the grand total per calculation item.

  • Burtonus's avatar
    Burtonus
    Regular Visitor
    1 year ago

    I ran into this same issue today (3 years later) and managed to find a workaround, which is: more calculation groups. To solve my problem, I just added a new calculation group where each calculation item is 

    Item = CALCULATE(SELECTEDMEASURE(),[Category] = "Item")

    with one total category which is just

    Total = SELECTEDMEASURE() --(or using a CALCULATE() to aggregate specific categories together)

    I was then able to add this as well as other calculation groups as different levels of a matrix column hierarchy.


    This workaround assumes two things: 1, that you have a manageable number of different categories to work with and 2, that your categories are static enough that you don't need to constantly update the calculation group. It doesn't appear that you can individually format calculation items, so this still won't look like the default Totals column, but at least the numbers are there.