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
Is your error caused by a division by zero? Try to already apply the BLANK() substitution there.
Some progress here. New PBIX file: https://drive.google.com/file/d/1pG3ua4BOWyIJhhh4dOwG9ac8UPWqUsYo/view?usp=sharing
This is a before screenshot
By changing the first above STDVX [measure] to the following by adding ALLNOBLANKROW first thing after summarize
VAR DEPRECIATION_VOL_OBSERVED =
STDEVX.S(
SUMMARIZE (
ALLNOBLANKROW('Helivalues Transaction History'),
'Helivalues Transaction History'[Sale Year]
)
, [Average Annualized Price change (weighted by Model Year Units)]
)
I get **a** sub-total rather than NaN. What that subtotal represents isn't entirely apparent, and isn't dynamic across the pivoted columns. It's summarized as 5.76% for each column and the grand total, rather than the average of what I perceive to be the available data points.
The intended output is for the {A01} pivot tabled-column to produce the average of the pivot table column immediate to its left. Based on what's shown {A01} should = 5.38% for 2016, 5.73% for 2017, 11.13% for 2018, 6.14% for 2019, and 6.14% for 2020.
- lbendlin5 years agoSuper User
"produce the average of the pivot table column immediate to its left"
Your measure has no idea what that means. "right" and "left" are meaningless unless you can help with an index column, or a guaranteed sort order.
- mrothschild5 years agoContinued Contributor
apologies. My language was colloquial and imprecise. For A01, I want the average of A for each of 2016, 2017, 2018, etc. Visually on the Matrix it's physically oriented to the left, but I want it associated with sale year.
- aj19735 years agoCommunity Champion
Hi mrothschild
If I may get involved in this, did you verify all the multitude of measures you have in your model??
Because I have seen many wrong doing like here, did you mean SUM or COUNT because it doesn't make sens to sum up the years!!!
here is the difference
also in your page only one slicer interacts with your Pivotal tabel
Date Table if it is not going to used why is it in the model!!
Sorry Pal, your model and your demande is not making it any clear to be solved.