Sum totals in matrix in power bi (grand total for matrix values)

[char23]

Hello, 

I am trying to create a matrix visual. I have it partly working. I have a main table in power bi that has three columns; the first two columns have unique data on [area] and [step] like what is show below (create, design, c1, d1, etc.) and the last column has a date. I also have a date table that is connected to the date column in the main table. I also have a measure the finds the last date associted with the first two columns and return a one. I want to be able to add a total to each column of the matrix (shown below in red) and sum all the returned 1s, and I want the grand total to stay the same if the matrix in collapsed. I also want the individual collapsed rows to show the sum of the expanded rows. I have included below example of what I am looking for. I also included the measure I currently have. On a side note, I also want the columns to show consecutive months, not just months were date is found from the measure. Thank you!

VAR LastDate =
CALCULATE(
MAX('Main Table'[Finish]),
ALLEXCEPT('Main Table', 'Main Table'[Area], 'Main Table'[Step])
)

VAR IsLastDate =
IF(MAX('Main Table'[Finish]) = LastDate, 1, BLANK())

RETURN
IF(
NOT(ISINSCOPE('Main Table'[Area])) && NOT(ISINSCOPE('Main Table'[Step])),
SUMX(VALUES('Main Table'[Area]),
SUMX(VALUES('Main Table'[Step]), IsLastDate)
),
IsLastDate
)

[DataNinja777 · Accepted solution]

Hi @char23 ,

To correctly sum totals in your Power BI matrix while ensuring that the grand total remains consistent when collapsed, you need to adjust your measure to properly aggregate the values. Your current measure is calculating the last date per Area and Step and returning 1 for rows where the date matches the latest date, but the issue arises when summing these values across the hierarchy.

A more structured approach is to first calculate the last date for each combination of Area and Step, then determine whether the current row corresponds to that last date. Finally, you need to ensure that the measure correctly aggregates values when collapsed by summing across all unique combinations of Area and Step. The following revised DAX measure accomplishes this:

VAR LastDate =
    CALCULATE(
        MAX('Main Table'[Finish]),
        ALLEXCEPT('Main Table', 'Main Table'[Area], 'Main Table'[Step])
    )

VAR IsLastDate =
    IF(MAX('Main Table'[Finish]) = LastDate, 1, BLANK())

VAR SumTotal =
    SUMX(
        SUMMARIZE(
            'Main Table',
            'Main Table'[Area],
            'Main Table'[Step],
            "Value", IsLastDate
        ),
        [Value]
    )

RETURN
    IF(
        ISINSCOPE('Main Table'[Step]),
        IsLastDate,
        SumTotal
    )

This measure ensures that the total at each level of the hierarchy correctly sums all 1s while maintaining the correct grand total even when the matrix is collapsed. It first determines the last date for each Area and Step, assigns 1 to rows matching the last date, and then calculates the correct total using SUMX(SUMMARIZE(...)). This approach prevents double counting and ensures that each total accurately represents the sum of expanded rows.

To ensure that all months appear in the matrix, you should use a proper Date table that contains a continuous range of dates and is related to 'Main Table'[Finish]. Instead of directly using Main Table[Finish] in the matrix columns, use DateTable[Month] to display consecutive months. If necessary, modify your measure to work with the full date range:

VAR SelectedMonth = SELECTEDVALUE('DateTable'[Date])

VAR LastDate =
    CALCULATE(
        MAX('Main Table'[Finish]),
        ALLEXCEPT('Main Table', 'Main Table'[Area], 'Main Table'[Step])
    )

VAR IsLastDate =
    IF(SelectedMonth = LastDate, 1, BLANK())

VAR SumTotal =
    SUMX(
        SUMMARIZE(
            'Main Table',
            'Main Table'[Area],
            'Main Table'[Step],
            "Value", IsLastDate
        ),
        [Value]
    )

RETURN
    IF(
        ISINSCOPE('Main Table'[Step]),
        IsLastDate,
        SumTotal
    )

This modification ensures that all months appear even when no data exists for a particular month. By using SELECTEDVALUE('DateTable'[Date]), the measure can work dynamically across all months in the date table. This ensures that your matrix consistently displays consecutive months, correctly rolls up totals, and maintains accurate grand totals when collapsed.

Best regards,

[char23]

Thank you very much! This is a huge help