Forum Discussion
Matrix sub-total rows not seemingly numerically associated with underlying data
PBIX FILE: https://drive.google.com/file/d/1j_4CrUj0fPh0xK6hnO7OZ-uIWkoP5Kdg/view?usp=sharing
________________________________________________
***UPDATE***
Identified one of the offending measures:
When OBSERVED_DEPRECIATION is the Matrix output, and it's programmed as follows:
VAR OBSERVED_DEPRECIATION =
SUMX(
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
The Matrix output looks like this (since I changed from AVERAGEX to SUMX, I would expect the Subtotals to accumulate:
When OBSERVED_DEPRECIATION is the Matrix output, and it's programmed as follows:
VAR OBSERVED_DEPRECIATION =
AVERAGEX(
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
When I change back to AVERAGEX(), the subtotals don't change.
___________________________________________________________________
PROBLEM: The values in the sub-total of the Matrix visual don't seem to be numerically associated with the underlying data that is supposed to be feeding them as highlighted in the red row below.
Intended output is ideally, the individual rows for the "Upside (Graph)" columns remain as shown, and the sub-total represents the average of the rows above it. Alternatively, I could create a calculated measure where the "Upside (purposefully for graphing)" column represents the average of all rows in the column directly to its left.
I'm trying to create a line chart from the data in the above Matrix.
The line on the line chart should be the average of the columns of "Upside (Graph) in the matrix. So the data point for 2019 should be (roughly from eyeballing) around $15,000,000. For reasons that are completely foreign to me, the datapoint on the line chart is pulling the $5,504,716 for the 2019 observation.
Even when I slice my PowerBI to individual rows associated with the Matrix, the row-subtotal changes slightly from what's shown above, but still isn't an average of a single datapoint = single datapoint.
The Measure to create the values for the Matrix table is relatively complex with VARs but the final calculation is:
MATRIX_OUTPUT =
(EARLIEST_OBSERVED_PRICE * (ABS(SIGN(SELECTED_DEPRECIATION)) * POWER((1 + SELECTED_DEPRECIATION),t)))
* COEFF_UPSIDE
EARLIEST_OBSERVED_PRICE =
EARLIEST_OBSERVED_PRICE =
CALCULATE (
[Average Unit Price Current Year (weighted by Model Year Units)],
'Helivalues Transaction History'[Sale Year] = EARLIEST_SALE_YEAR
)
EARLIEST_SALE_YEAR =
EARLIEST_SALE_YEAR =
CALCULATE(
MIN('Helivalues Transaction History'[Sale Year]),
ALLSELECTED('Helivalues Transaction History'[Sale Year])
)
SELECTED_DEPRECIATION =
SELECTED_DEPRECIATION =
DIVIDE(
CALCULATE(
AVERAGE('Input - Depreciation'[Input - Depreciation]),
ALLSELECTED('Input - Depreciation'[Input - Depreciation])
),
100
)
t =
t =
CURRENT_SALE_YEAR - EARLIEST_SALE_YEAR
note, I thought/think this could be an issue, with the AVERAGE() not being the correct function to pull in what I'd want, but even if I used SELECTEDVALUE() the same output results
CURRENT_SALE_YEAR =
CURRENT_SALE_YEAR =
AVERAGE('Helivalues Transaction History'[Sale Year])
COEFF_UPSIDE =
COEFF_UPSIDE =
POWER( ABS( SQRT( ABS (1 + SELECTED_DEPRECIATION + 2 * VOLATILITY) ) ) , t )
VOLATILITY =
VOLATILITY =
ROW_STANDARD_DEVIATION
* POWER(
(1 +
DIVIDE( ABS(SELECTED_DEPRECIATION - OBSERVED_DEPRECIATION) ,
ABS(OBSERVED_DEPRECIATION)
)
) ,
2)
ROW_STANDARD_DEVIATION =
ROW_STANDARD_DEVIATION =
STDEVX.P(
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
OBSERVED_DEPRECIATION =
OBSERVED_DEPRECIATION =
AVERAGEX(
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
2 Replies
- AnonymousNot applicable
Hi mrothschild ,
Sorry, I did not particularly understand your question. What is your expected result? Your data is too large for me as a stranger. Can you simplify the problem, give a simple sample data and expected results, I will help you as much as possible.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- mrothschildContinued Contributor
Anonymous
Here is a cleaned-up version of above: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing
The tab in question is "Troubleshooting for forum help". The intent is to have the cells/values in the bottom matrix equal the columnar average of the above Matrix. As shown in the screenshot below, the "Aggregated Model" slicer located on the left hand of the dashboard in the middle (from top to bottom) has selected only "EC-135".
The current programming I'm attempting to use that generates the [measure] for the lower Matrix is:
Upside (averaged within a given sale year for graph output) = VAR OUTPUT = AVERAGEX( ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ('Helivalues Transaction History'[Sale Year]), 'Helivalues Transaction History'[Sale Year], "COUNT", COUNT ( 'Helivalues Transaction History'[Year of Build] ) ), "__Output", [Upside (Graph)] ), [__Output] ) RETURN OUTPUT