Forum Discussion
Matrix sub-total calculation?
- 5 years ago
Final PBIX File here: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing
Code to create the weighted average measure:
WAVG Measure = VAR DESIRED_ROWS = FILTER( 'Table', 'Table'[Column1] > 0 && 'Table'[Attribute (from a PowerQuery unpivot] = "attribute1 selected" || 'Table'[Attribute (from a PowerQuery unpivot] = "attribute2 selected" || 'Table'[Attribute (from a PowerQuery unpivot] = "attribute3 selected" || ) RETURN DIVIDE( SUMX ( DESIRED_ROWS, 'Table'[Denominator Column] * 'Table'[Value] ), SUMX ( DESIRED_ROWS, 'Table'[Denominator Column] ) )Code to generate Matrix values from a weighted average measure:
_A = // Note - below one can use any of the *X DAX functions, such as SUMX, AVERAGEX, MEDIANX, STDEVX.S, etc. VAR OUTPUT = AVERAGEX( SUMMARIZE ( ALLSELECTED('Table'), 'Table'[Row Column] ) , [WAVG Measure] ) RETURN OUTPUTCode to summarize the Matrix value [measure]
_A Summarized = // Choose AVERAGEX or SUMX below depending on one's needs VAR OUTPUT_CELL = AVERAGEX( SUMMARIZE('Table','Table'[Matrix Row Column]) , [WAVG measure] ) RETURN OUTPUT_CELL
When you create measures that you plan to use in a matrix visual you need to consider that the matrix visual does FOUR computations:
- individual cell
- column total
- row total
- grand total
Things like "SELECTEDVALUE()" have no meaning in the totals. You cannot use that. The totals most often need to use iterator functions like AVERAGEX to make sense. When you run into these issues often enough it is worth reminding yourself of the guidance: "Think like the Grand Total". Very often a measure that works for a Grand Total will also work for the other totals and for the individual cells. The other way round - not so much.
Can you formulate what should happen in each of the four scenarios?