Forum Discussion
Aveeno111
6 years agoFrequent Visitor
Calculating weighted percentiles
What is the best way to calculated a weighted percentile value in Power BI? I am trying to create a histogram that shows income percentiles from survey data that is weighted. The data set basical...
mahoneypat
Microsoft Employee
6 years agoThat 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
Anonymous
4 years agoNot applicable
Very eligant weighted median calcualtion, thanks!