missing subtotal for running total in matrix

[mahendi]

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

Category	Sub Category	Sub Category 2	Amount	Date
1	1.1	1.1.1	1	01/01/20
1	1.2	1.2.1	1	01/02/20
1	1.3	1.3.1	1	01/03/20
1	1.1	1.1.2	1	01/04/20
1	1.2	1.2.2	1	01/05/20
1	1.3	1.3.2	1	01/06/20
2	2.1	2.1.1	1	01/06/20
2	2.1	2.1.2	1	01/05/20
2	2.2	2.2.2	1	01/04/20
2	2.2	2.2.1	1	01/03/20
2	2.3	2.3.1	1	01/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?

[MFelix]

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?

[mahendi]

yes correct @MFelix 

[MFelix · Accepted solution]

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.

[mahendi]

@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]

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]

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.