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.   Category Sub Category Sub Category 2 Amount Date 1 1.1 1.1.1 1 ...
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.

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.