topic Re: Matrix Grand Total - Summing the Row Averages in DAX Commands and Tips

Matrix Grand Total - Summing the Row Averages

MeyerRy — Tue, 04 Jun 2024 13:10:41 GMT

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

)

Re: Matrix Grand Total - Summing the Row Averages

MeyerRy — Mon, 03 Jun 2024 17:12:56 GMT

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

Re: Matrix Grand Total - Summing the Row Averages

Anonymous — Tue, 04 Jun 2024 03:17:36 GMT

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.

Re: Matrix Grand Total - Summing the Row Averages

MeyerRy — Tue, 04 Jun 2024 13:17:27 GMT

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.

Re: Matrix Grand Total - Summing the Row Averages

MeyerRy — Tue, 04 Jun 2024 17:47:46 GMT

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) ) )