Forum Discussion

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

missing subtotal for running total in matrix

I have the following matrix and table, the subotal is missing in the running totals where the category is empty.

 

CategorySub CategorySub Category 2AmountDate
11.11.1.1101/01/20
11.21.2.1101/02/20
11.31.3.1101/03/20
11.11.1.2101/04/20
11.21.2.2101/05/20
11.31.3.2101/06/20
22.12.1.1101/06/20
22.12.1.2101/05/20
22.22.2.2101/04/20
22.22.2.1101/03/20
22.32.3.1101/02/20

 

I used quick measure for running total - 

Amount running total in Category =
CALCULATE(
    SUM('Sheet1'[Amount]),
    FILTER(
        ALLSELECTED('Sheet1'[Category]),
        ISONORAFTER('Sheet1'[Category], MAX('Sheet1'[Category]), DESC)
    )
)
 
is there a way to display the running total in subtotal where the category has no values?
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Don'@mahendi,

    I think this has to do with the structure of the data, what I did was this:

    • A new column has been created that joins the category - subcategory - Subcategory2
    ID_CAT = 'Table'[Category]&'Table'[Sub Category]&'Table'[Sub Category 2]
    • A new table has been created with the categories
    IDCAT = SUMMARIZE('Table';' Table'[Category];' Table'[Sub Category];' Table'[Sub Category 2];' Table'[ID_CAT])

    Now create the following measure:

    Total Value = CALCULATE(sum('Table'[Amount]); FILTER(ALLSELECTED(IDCAT[Category]);IDCAT[Category]<= MAX(IDCAT[Category])))

    Result below and in attach PBIX file.

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi mahendi ,

     

    The calculation you have is correct based on the matrix setup and the measure.

     

    In your measure you are picking all the values that are On or After your Maximum category value, looking at the image you have for category 1 subtotal the value 1 for category 2 there is no value so the Sub Total value for that category is blank and it cannot be 1.

     

    Believe you are looking at the sub total at a different way, I have made some changes to the setup of the matrix and renamed the totals:

     

    Has you can see the lines that refer to subcategory is blanks.

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

    MFelix  Thank you for the suggestion but in this particular scenario the display sequence is important.

    I am trying to display running total in the sub total rows. if a category is empty it should still carry the total from previous sub total as it is a running total.

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      5 years ago

      Hi mahendi,

       

      Then the issue is with your formula you are only picking up values that are above the category you must use a sumx for the subtotals. 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi  mahendi ,

     

    Do you want a sub total for every subcategory so looking at your data in January all subtotals show 1 on February first will show 1 next will show 2 and so on?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Don'@mahendi,

        I think this has to do with the structure of the data, what I did was this:

        • A new column has been created that joins the category - subcategory - Subcategory2
        ID_CAT = 'Table'[Category]&'Table'[Sub Category]&'Table'[Sub Category 2]
        • A new table has been created with the categories
        IDCAT = SUMMARIZE('Table';' Table'[Category];' Table'[Sub Category];' Table'[Sub Category 2];' Table'[ID_CAT])

        Now create the following measure:

        Total Value = CALCULATE(sum('Table'[Amount]); FILTER(ALLSELECTED(IDCAT[Category]);IDCAT[Category]<= MAX(IDCAT[Category])))

        Result below and in attach PBIX file.