powerbiaggregatepercentile
Apply PERCENTILE.INC on aggregated results in Power BI
I have the following dataset 'Table1'
| City | Brand | Year | Number_of_Customers |
|---|---|---|---|
| London | A | 2019 | 387 |
| London | A | 2020 | 1566 |
| London | A | 2021 | 1409 |
| Manchester | A | 2019 | 353 |
| Manchester | A | 2020 | 679 |
| Manchester | A | 2021 | 1099 |
| Bristol | A | 2019 | 2999 |
| Bristol | A | 2020 | 2654 |
| Bristol | A | 2021 | 426 |
| York | A | 2019 | 214 |
| York | A | 2020 | 948 |
| York | A | 2021 | 1948 |
| Birmingham | A | 2019 | 452 |
| Birmingham | A | 2020 | 2465 |
| Birmingham | A | 2021 | 1856 |
| London | B | 2019 | 1829 |
| London | B | 2020 | 1236 |
| London | B | 2021 | 2960 |
| Manchester | B | 2019 | 2593 |
| Manchester | B | 2020 | 533 |
| Manchester | B | 2021 | 126 |
| Bristol | B | 2019 | 1588 |
| Bristol | B | 2020 | 2067 |
| Bristol | B | 2021 | 1823 |
| York | B | 2019 | 1667 |
| York | B | 2020 | 2931 |
| York | B | 2021 | 657 |
| Birmingham | B | 2019 | 2896 |
| Birmingham | B | 2020 | 421 |
| Birmingham | B | 2021 | 2488 |
I wish to apply PERCENTILE.INC on the following 'Table2' where [Number_Of_Customers] is aggregated on [Brand] and [Year] level
| City | Number_Of_Customers |
|---|---|
| Birmingham | ... |
| Bristol | ... |
| London | ... |
| Manchester | .... |
| York | .... |
where in 'Table2' I filter columns [Brand] and [Year] with multi-valued filters,
e.g. for [Brand] = "A" and [Year] = {"2019","2020"} the 75th percentile should be 2917 (I get this result from EXCEL function PERCENTILE.INC).
So far used the following formula
75th_Percentile =
CALCULATE(
PERCENTILE.INC(Table1[Number_of_Customers],0.75),
ALL('Table1'[City])
)
but this doesn't work ( see image below where I get wrong value [75th_Percentile] = 2240,25 ).
Any ideas how to solve this?
Solution
Second try:
75th_Percentile =
VAR a = CALCULATETABLE(ADDCOLUMNS( SUMMARIZE( Table1 , Table1[City]), "@total", CALCULATE(SUM(Table1[Number_of_Customers]))), ALLSELECTED(Table1))
RETURN PERCENTILEX.INC (a, [@total],0.75)
First try:
What happens if you remove the calculate?
75th_Percentile =
PERCENTILE.INC(Table1[Number_of_Customers],0.75)
