I'm trying to convert an excel report to Power BI:
I've got a table of monthly sales by country and city, and they need to be grouped in percentile buckets. The buckets aren't equidistant, it's set up in a gradient [0, 1, 5, 10, 15, 20, 25, 30, 40...70, 75, 85, 90, 95, 99]. They're stored as a seperate lookup table and then are referenced to build the counts, which I originally did via python and inserted into the final excel spreadsheet.
| City Name | 0th Percentile | 1st Percentile | 5th Percentile | 10th Percentile | 15th Percentile | 20th Percentile | 25th Percentile | 30th Percentile | 40th Percentile | 50th Percentile | 60th Percentile | 70th Percentile | 75th Percentile | 80th Percentile | 85th Percentile | 90th Percentile | 95th Percentile | 99th Percentile |
| Barcelona | $360,000 | $520,000 | $645,000 | $715,000 | $770,000 | $795,000 | $825,000 | $855,000 | $920,000 | $985,000 | $1,060,000 | $1,120,000 | $1,170,000 | $1,230,000 | $1,280,000 | $1,370,000 | $1,540,000 | $2,230,000 |
| Paris | $85,000 | $120,000 | $360,000 | $460,000 | $545,000 | $600,000 | $660,000 | $720,000 | $780,000 | $860,000 | $950,000 | $1,070,000 | $1,130,000 | $1,210,000 | $1,350,000 | $1,570,000 | $1,930,000 | $2,840,000 |
| London | $45,000 | $160,000 | $250,000 | $300,000 | $330,000 | $350,000 | $370,000 | $390,000 | $445,000 | $530,000 | $645,000 | $765,000 | $815,000 | $885,000 | $995,000 | $1,140,000 | $1,340,000 | $1,990,000 |
The end result needs to look like the table below, with the number of sales for each city by percentile bucket. Is this doable in Power BI? I'm pretty new to Power BI so I'm not sure if it needs to be done via DAX or in the transform data window (maybe a merge or related column...?). Haven't seen anything on the forum or youtube that quite covers this (a lot of group and counts by dynamic dates) but if there is I'd love to be pointed in the right direction.
| | Sales Counts by Percentile |
| City | 0 | 1 | 5 | 10 | 15 | 20 | 25 | 30 | 40 | 50 | 60 | 70 | 75 | 80 | 85 | 90 | 95 | 99 |
| Dublin | 3 | 11 | 12 | 11 | 3 | 7 | 5 | 13 | 7 | 6 | 4 | 2 | 6 | 2 | 4 | 4 | 2 | 0 |
| London | 0 | 3 | 2 | 0 | 4 | 6 | 4 | 4 | 7 | 3 | 4 | 1 | 3 | 2 | 0 | 2 | 2 | 0 |
| Paris | 0 | 11 | 17 | 24 | 14 | 13 | 10 | 27 | 34 | 33 | 29 | 11 | 19 | 22 | 16 | 18 | 12 | 0 |
| Barcelona | 0 | 34 | 51 | 63 | 53 | 36 | 34 | 57 | 87 | 82 | 50 | 37 | 20 | 42 | 26 | 23 | 11 | 5 |
| Athens | 1 | 14 | 11 | 10 | 14 | 10 | 13 | 18 | 24 | 20 | 14 | 7 | 7 | 7 | 8 | 3 | 2 | 0 |
