Forum Discussion
mahendi
Helper I
Helper Imissing 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. Category Sub Category Sub Category 2 Amount Date 1 1.1 1.1.1 1 ...
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.
MFelix
Super User
Super UserHi 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
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.