Forum Discussion
Calculating weighted percentiles
mahoneypat
Thanks for taking the time to propose a revised solution. Unfortunately, the code still does not work because the weights vary greatly, from the single digits to the thousands.
I did find an alternative method that seems to work here:
WeightedPercentile_0.5 =
MINX(
FILTER(
VALUES('Balances'[INCOME]),
CALCULATE(
SUM('Balances'[WGT]),
'Balances'[INCOME] <= EARLIER('Balances'[INCOME])
)
> (0.5*SUM('Balances'[WGT]))
),
'Balances'[INCOME]
)
A new challenge is that the performance remains quite slow given the size of my table, which is 9 million rows of data.
I never actually need to calculated the weighted percentile across the entire 9 million rows (there are 9 years of data x 40 products). The most I actually ever really want to calculate it over is closer to 30,000 since I would never want to combine years or products.
Is there a way to make my code more efficient?
Microsoft EmployeeThat is a simpler approach. I thought you needed a percentile calculation specifically, so tried to match that. First thing to try for improved performance is to pull the filter evaluation out into a variable like this
WeightedPercentile_0.5 =
VAR pct50 =
0.5 * SUM ( 'Balances'[WGT] )
RETURN
MINX (
FILTER (
VALUES ( 'Balances'[INCOME] ),
CALCULATE (
SUM ( 'Balances'[WGT] ),
'Balances'[INCOME] <= EARLIER ( 'Balances'[INCOME] )
) > pct50
),
'Balances'[INCOME]
)
That should help, but it still has a lot of calculation going on. If you don't need it to be responsive to slicers, you could precalculate most of it as a calculated table. You still could get different percentiles from that table, but the heavy calculating would already be done at refresh.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat
Thanks again for quick response.I do need the measure to be dynamic (i.e. so if I cut it by year or product, it calculates properly). I implemented your suggested change, but it ultimately doesn't refresh quicker.
I think the challenge is that the calculation of the cumulative values is just a lot of computations (since it calculates for each row). What's weird is that if I cut the original dataset down (in PowerQuery) to a particular cut of the data, it runs much faster. I would have thought that whether my calculation was performed on 30,000 rows from a table with only 30,000 rows or 30,000 rows filtered from a table with from9 million rows, that it would perform at a similar speed. Am I wrong about that?
- mahoneypat
Performance can be very different (as you've seen). You can watch some of the videos by SQLBI on optimizing measures to understand more about the storage engine and formula engine, and how DAX expressions are evaluated.
One other approach you could consider is to also create a summarized version of your table (just Income and Total Weight maybe) and relate it to your original table. That way you can still slice/dice from your main table, but pass the filtered values to the summarized for more efficient analysis.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Very eligant weighted median calcualtion, thanks!