Forum Discussion

mahendi's avatar
mahendi
Icon for Helper I rankHelper I
5 years ago

subtotal missing for running total in matrix

The subtotal cell remians empty for running totals if there is no value in that category.

I tried using the cross join method but it adds all the sub categories in all the categories, I dont want to mix up the sub categories.

 

 please suggest a solution.

Need Help

6 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi mahendi ,

     

    What's your DAX expression of the measure/column used in "Value" field of the Matrix visual?

     

     

    Best regards

    Icey

     

    • mahendi's avatar
      mahendi
      Icon for Helper I rankHelper I
      5 years ago

      Icey  I used quick measure running total.

       

      Amount running total in Category =
      CALCULATE(
          SUM('Sheet1'[Amount]),
          FILTER(
              ALLSELECTED('Sheet1'[Category]),
              ISONORAFTER('Sheet1'[Category], MAX('Sheet1'[Category]), DESC)
          )
      )
    • mahendi's avatar
      mahendi
      Icon for Helper I rankHelper I
      5 years ago

      Icey  I intentionally took that record out to illustrate the issue. if you remove the record, the subtotal goes blank, as I am using running total, the total should remain 1 and not blank. Please advice a solution.

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi mahendi ,

         

        Sorry to reply late.

        I find the cause: there is no record of "Category 2 in January".

        You need to create another Category table with all categories and another Dates table with all months. Then, create relationship among the three tables. Finally, use the column "Category" from the new Category table, the column "Month" from the new Dates table, and you will get what you want.

         

         

        Best regards

        Icey

         

        If this post helps, then consider Accepting it as the solution to help other members find it faster.