Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Sample Excel data: https://docs.google.com/spreadsheets/d/1o55R8MKW263heweuKcZPIvEBgyBoC5hL/edit?usp=sharing&ouid=11511...
The attached file has a pivot table on it, showing [% change] as the Values, [Sale Year] as columns, and [Year of Build] as rows.
This screenshot of a PowerBI matrix shows the average and standard deviation (i.e, "volatility") of each row of the pivot table.
The [measure] to calculate the average is
Average of Rows =
AVERAGEX (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
The standard deviation of the rows =
Standard Deviation of Rows =
STDEVX.S (
SUMMARIZE (
'Helivalues Transaction History',
'Helivalues Transaction History'[Sale Year],
'Helivalues Transaction History'[Year of Build]
),
[Average Annualized Price change (weighted by Model Year Units)]
)
I'm trying to create a new Matrix, as one part of a larger calculation, where the Standard Deviation of the above rows is represented in each of the locations shown below.
Questions:
1) In the red-shaded row, you'll see a bunch of NaN. How do I get that row, which is a Matrix sub-total, to produce the average of the data lying above it, and still retain the data above. I'm okay if this is generated in a completely different [measure] that creates a new column of identical data.
Solved! Go to Solution.
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 OUTPUT
Code 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?
Thank you so much for your response and super fantastic simple-to-understand explanation. I've had a hard time distinguishing between [column] (as opposed to [measure] or [table] and "column" as in of a pivtotable.
In this case, I'm trying to produce a measure that provides the average of the pivottable column for each Year of Sale where errors/NaNs are treated as BLANK(). That measure should also sub-total to the average of the rows when across rows, and should be the average of each of the individual cells as a Grand Total.
In the past few days, I've figured out that this formula works:
Average of pivoted column
VAR OUTPUT =
AVERAGEX(
ADDCOLUMNS (
SUMMARIZE (
ALLSELECTED ('Table'[Table column]),
'Table'[Table column],
"SUM", SUM ( 'Table'[Table row] )
),
"Percent", CALCULATE ( SUM ( 'Table'[Table row] ) )
),
[measure of the pivot column value]
)
RETURN
OUTPUT
But in some of my pivottable columns, where I use IFERROR({calc}, BLANK() ) the above-formula returns blank in all instances.
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.
"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.
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.
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Here is a cleaned up version of the PBIX: https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing
Here is the current formula that's not generating the intended output:
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
Here is the current output:
The intended output is that the Matrix at the bottom of the above-screenshot is populated with the columnar average of the Matrix at the top of the above-screenshot.
The formula for the [measure] in the top Matrix is:
Upside (Graph) =
VAR EXPECTED_PRICE =
IF(
HASONEVALUE('Helivalues Transaction History'[Year of Build])
, [Expected (Graph)]
, AVERAGEX('Helivalues Transaction History',[Expected (Graph)])
)
VAR COEFF_UPSIDE =
IF(
HASONEVALUE('Helivalues Transaction History'[Year of Build])
, [______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}]
, AVERAGEX('Helivalues Transaction History',[______G1 - Coeff_Upside {equals} sqrt{power{{E},{F}}}])
)
VAR UPSIDE_GRAPH =
EXPECTED_PRICE * COEFF_UPSIDE
VAR OUTPUT_FINAL =
AVERAGEX(
ADDCOLUMNS (
SUMMARIZE (
ALLSELECTED ('Helivalues Transaction History'[Year of Build]),
'Helivalues Transaction History'[Year of Build],
"SUM", COUNT ( 'Helivalues Transaction History'[Sale Year] )
),
"__Output", UPSIDE_GRAPH
),
[__Output]
)
RETURN
IF(
HASONEVALUE('Helivalues Transaction History'[Year of Build]),
OUTPUT_FINAL
, AVERAGEX('Helivalues Transaction History',OUTPUT_FINAL)
)
Hey @mrothschild
I spent some time to see why the totals are not accurate, here is your file where I added a new page and corrected a tiny view:
https://drive.google.com/file/d/1svcBN0vf1oEPSimJ60GibzMJaDvjhtNn/view?usp=sharing
A quick ex
Looking at the measure it is like you are dividing (X/Y) * X = Y all the time.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thank you for that. I can confirm that "YOUR TABLE" in the PBI file your shared is the intended outcome. That calculation is a version of SUMPRODUCT in Excel, where it's delivering the weighted-average price based on units in a given year.
Imagine it was sneakers and we were selling some for $50 and some for $100. If we sold twice as many $50 sneakers our "Total Sales" would be the same for each sneaker type, but the weighted average price would be $67, not $75. At an individual row level, you would be always, in your example be dividing by Y and left with X, but at a summarized aggregated level, the math changes.
Sneaker Price | Units Sold | Revenues | Price per Sneaker sold |
$50 | 10 | $500 | $50 |
$100 | 5 | $500 | $100 |
SUBTOTAL | 15 | $1000 | $67 |
Sorry but I think you didn't get my point. To fix an issue you need to go step by step with your measures and test them one by one until you get to that issue.
For instance the measure "Upside (Graph)" is full of variables and none of them makes sense to me therfore how do you know that the values in the Matrix are accurate and if so what makes the total coming out wrong???
Please re read the remarks in the file I sent you and follow the actions i made to get to the confirmation that "MY TABLE" was close to your inteded income.
Just to add, it is good to use Variables but it's a must that you know what those variables are going to do and how to use them because sometimes Var don't work as intended when it is embeded inside CALCULATE(as an example). So adding simple and small measures and then combine them into one can be more accurate that using multiple VAR inside a single measure.
Good luck
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Unless the calculation results in something different for your file than for the one you shared with me, "YOUR TABLE" is producing the desired output, and "MY TABEL" is not.
But you have peeled back to identify the core problem, which is the methods I've been reading about that are used to summarize a [measure] calculated using a simple average don't seem to be working for [measures] calculated using a weighted average approach.
The following matrix In this file: https://drive.google.com/open?id=12_k_tO8BdrXV73Lbho_whabGP-jOlasP
is my attempt at a [measure] by [measure] approach. The _TEST columns are based on simple averages and not weighted averages and the methodology to get to the desired row sub-totals is working.
I need to figure out how to do the same for a weighted average [measure].
I think I've found the solution. Will post here when done, but please stop looking at this for now.
Premature, but making progress.
The new interim step measure, {A int1} goes onto the matrix in individual cells as [Units] * [{A}] and is calculating correctly at an individual cell level. The [Units] is calculating correctly at both individual and row-subtotal level.
So now trying to summarize {A int1}, consistently associated with the individual cell data that is going into it.
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 OUTPUT
Code 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
Hi @mrothschild
Before fixing the second Matrix, are you sure the values in the first Matrix are accurate?
same here
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
PBIX file is here; https://drive.google.com/file/d/12_k_tO8BdrXV73Lbho_whabGP-jOlasP/view?usp=sharing
Excel file with exported results from upper Matrix: https://docs.google.com/spreadsheets/d/12nChVgYinw5RMB-M5S4lv96hLQQzMwjT/edit?usp=sharing&ouid=11511...
____________________________________________
I've added a new tab, called "Troubleshooting for forum help - buildup. . . "
This indicates that the upper matrix in the original screenshot below is all "fruit from a poisonous tree" at a summarized level but not at an individual cell level, which I believe is what you were driving at @aj1973 .
The data in each of the individual cells is accurate/desired/intended. The summarized columnar average of these antecedent measures is wrong.
The programming for each of these [measures] is all structured the same, so I'm hoping if I can fix this structure everything else will fall into place. {A} uses STDEVX.S, but STDEVX.P also doesn't summarize to the desired output. {B} & {C} use AVERAGEX, and {C} isn't summarizing at all, i.e., BLANK() in one of the sub-total rows, even though two individual cells are above it, and is completely off when being summarized elsewhere - a bunch of negative numbers being summarized to a positive number.
{D}, {E}, and {G} are all functions of {A}, {B}, and {C}, so unless the first 3 summarize correctly, I imagine will be hard for these to summarize correctly.
{F} is summarizing correctly.
{A, B, C, etc.} =
VAR TARGET_MEASURE=
AVERAGEX(
SUMMARIZE (
ALLNOBLANKROW('Helivalues Transaction History'),
'Helivalues Transaction History'[Sale Year]
)
, [Average Annualized Price change (weighted by Model Year Units)]
)
RETURN
IF(
HASONEVALUE('Helivalues Transaction History'[Year of Build])
, TARGET_MEASURE ,
AVERAGEX('Helivalues Transaction History', TARGET_MEASURE)
)
More broadly, I apologize for my inability to articulate the issues clearly and the idiosyncracies in the coding. I'm relatively new to DAX and don't have an intuitive understanding. Most of what I've built is equivalent to duct tape and chicken wire that come from hitting a wall, going to the forums, and trial & error until I find something that works.
Thanks for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |