Matrix Grand Total - Summing the Row Averages

[MeyerRy]

I need help getting a measure to sum the row totals for the grand total value. The measure below produces the following output. The grand total box is taking averaging the 3 column totals (highlighted in yellow). I want the grand total to sum the row totals (highlighted in green). How can I accomplish this?

Test2 =

AVERAGEX(

    VALUES(EstimateSummaryDataDetails[EstimateName]),

    CALCULATE(SUM(CostUnpivot[TotalCost]))

)

[MeyerRy · Accepted solution]

I managed to get a working measure based on the help from @Anonymous. You helped point me in the right direction. The following measure will give me the answer I was looking for but what I failed to anticapte was if I changed the cost breakdown structure (the rows on the left) to a different one that had more blanks in the data. The Grand Total cost will change. Which technically is doing what I wanted but from a user standpoint the Grand Total cost changing will immediatdly cause distrust with the data.  What I am going to end up doing is just taking the total cost of each row and dividing by number of projects to get the average cost. That is a much easier measure to create. I am not sure if this will help others in the future but here is the measure:

Total Cost with Grand Total Summing Matrix Row Totals = 
VAR RowTotal = 
    SUMX(
        SUMMARIZE(
            'Table',
            'Table'[CBSValue],
            "@TotalCost",
                SUMX(
                    FILTER(
                        'Table',
                        'Table'[CBSValue] = EARLIER('Table'[CBSValue])
                    ),
                    IF(NOT(ISBLANK('Table'[TotalCost])), 'Table'[TotalCost], BLANK())
                )
        ),
        [@TotalCost]
    )
VAR NonBlankCount = 
    SUMX(
        SUMMARIZE(
            'Table',
            'Table'[CBSValue],
            "@Count",
                COUNTX(
                    FILTER(
                        'Table',
                        'Table'[CBSValue] = EARLIER('Table'[CBSValue])
                    ),
                    IF(NOT(ISBLANK('Table'[TotalCost])), 'Table'[TotalCost], BLANK())
                )
        ),
        [@Count]
    )
VAR RowAverage = 
    DIVIDE(RowTotal, NonBlankCount, 0) -- Divide by NonBlankCount, if it is 0 then use 0 as default
RETURN
IF(
    ISINSCOPE('Table'[CBSValue]), 
    RowAverage,
    SUMX(
        SUMMARIZE(
            'Table',
            'Table'[CBSValue],
            "@RowTotal",
                SUMX(
                    FILTER(
                        'Table',
                        'Table'[CBSValue] = EARLIER('Table'[CBSValue])
                    ),
                    IF(NOT(ISBLANK('Table'[TotalCost])), 'Table'[TotalCost], BLANK())
                ),
            "@NonBlankCount",
                COUNTX(
                    FILTER(
                        'Table',
                        'Table'[CBSValue] = EARLIER('Table'[CBSValue])
                    ),
                    IF(NOT(ISBLANK('Table'[TotalCost])), 'Table'[TotalCost], BLANK())
                )
        ),
        DIVIDE([@RowTotal], [@NonBlankCount], 0)
    )
)

[MeyerRy]

Here is a link to a test power BI dashboard with the data added in. Hopefully that will help out.
TestData.pbix

[Anonymous]

Hi @MeyerRy ,

I create a table as you mentioned.

Then I create two measures and here are the DAX codes.

Column = SUMX('Table','Table'[A]+'Table'[B]+'Table'[C])/3

Row =
(
    MAX ( 'Table'[A] ) + MAX ( 'Table'[B] )
        + MAX ( 'Table'[C] )
)
    / (
        COUNT ( 'Table'[A] ) + COUNT ( 'Table'[B] )
            + COUNT ( 'Table'[C] )
    )

Finally I create a measure and get what you want.

Column+Row = 'Table'[Column]+'Table'[Row]

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MeyerRy]

@Anonymous Interesting formulas. Unfornataly the $88.56 M is not the number I was looking for. If you add up all the row totals the grand total should be $89,389,186.