Coloring percentiles with a stacked column chart

[061elliven]

Hi,

There are products, that come in weekly, and I want to visualise, how long these products are processed. I want to visualise this with a stacked column chart, where there is amount of products on the y-axis, week number on the x-axis, and bars are colored based on percentiles. Picture of the desired goal can be seen below. For example, >0.8 means, that processing of the products took more time than with 80 % of the cases. 

Simplified, my data is like

ProductID, ProductArrivalDate, ProductReadyDate, ArrivalYear, ArrivalWeek

Unfortunately, I cannot include productIDs, because they are classified, and thus cannot share the data.

Then, I create a new column 'daysTaken', by

daysTaken = DATEDIFF(Table[ProductArrivalDate],Table[ProductReadyDate], DAY).

This seems to work fine, below you can see a screenshot from the table. Data type and format is 'Whole number'.

However, I have problems with the percentiles. I have tried few approaches:

Approach one, counting measure for each percentile, and creating SWITCH column, which classifies products based on these percentiles:

First, I created measure for each percentile (following solution of https://community.powerbi.com/t5/Desktop/Percentile-exc-Error/m-p/1927498/highlight/true), below is and example for class '>0' (below 0.2 percentile). Apologies for the formatting, I could not get HTML to work:

Measure =

VAR k = 0.2

// Both COUNT and PERCENTILEX.EXC ignore blanks

VAR NumValues =

    COUNT ( Table[daysTaken] )

VAR LowerBound = 1 / ( NumValues + 1 )

VAR UpperBound = NumValues / ( NumValues + 1 )

RETURN

    IF (

        AND ( k >= LowerBound, k <= UpperBound ),

        PERCENTILE.EXC ( Table[daysTaken], k)

    )

This also seems to give reasonable results (screenshot below). Again, data formats are 'Whole number'.

However, when I connect these measures to the products with

percentiles1 =

SWITCH (

    TRUE (),

    Table[daysTaken]<[Measure], ">0",

    Table[daysTaken]<[Measure 2], ">0.2",

    Table[daysTaken]<[Measure 3], ">0.4",

    Table[daysTaken]<[Measure 4], ">0.6",

    ">0.8"

            )

Every row is labeled as '>0.8', as can be seen below.

If I fix the measures to given values, I get the example picture in the beginning, and everything works fine. For example if I write Table[daysTaken]<25, ">0". However, I have many processes, and thus the percentiles change, so I cannot fix the values.

Approach two, count percentile for each product:

Next, I tried to count percentile for each product (like solution in https://community.powerbi.com/t5/Desktop/how-to-calculate-percentile-and-values-for-rows-in-a-column...

Percentiles =
VAR _ROW1 = CALCULATE(COUNTROWS(Table),Table[daysTaken]<=EARLIER(Table[daysTaken]))
VAR _ROW2 = CALCULATE(COUNTROWS(Table),ALL(Table))
RETURN
_ROW1 /_ROW2

But for some reason, it gives '0' for every product. I tried to figure this out, and it seems like for some reason _ROW1 counts only amount of rows, that have the same 'daysTaken' value. If I make it to return only _ROW1, you can see how there are only 4 rows for number 8, which equals amount of 8s in the picture above.

So, any idea what am I doing wrong, or how I could easily get the percentiles? Sorry for the long post, but I'm out of potatoes.

[061elliven · Accepted solution]

If someone in the future is interested, I finally solved this with similar approach as my approach two.

First, I created a new column where I counted exact percentiles for each product with

exactPercentiles =

VAR _ROW1 = COUNTROWS(FILTER(Table, Table[daysTaken]<=EARLIER(Table[daysTaken]) && Table[process]=EARLIER(Table[process])))

VAR _ROW2 = COUNTROWS(FILTER(Table, Table[process]=EARLIER(Table[process])))

RETURN

_ROW1 / _ROW2

Next, I used this exact percentile to classify products to five classes in a new column:

percentileClasses =

SWITCH (

    TRUE (),

    Table[exactPercentiles]<0.2, ">0 %",

    Table[exactPercentiles]<0.4, ">20 %",

    Table[exactPercentiles]<0.6, ">40 %",

    Table[exactPercentiles]<0.8, ">60 %",

    ">80 %"

            )

FInally, I used this column as a legend in my stacked column chart.

[061elliven]

Here is some test dataset, if it helps

ArrivalTime	ResultTime	Year	ArrivalWeek	ProductID
3.1.2022 11:02	9.2.2022 15:14	2022	2	1
3.1.2022 11:04	9.2.2022 15:28	2022	2	2
3.1.2022 11:05	9.2.2022 15:30	2022	2	3
3.1.2022 11:07	9.2.2022 15:31	2022	2	4
25.1.2022 7:01	22.2.2022 10:45	2022	5	5
5.1.2022 13:21	9.3.2022 8:08	2022	2	6
25.1.2022 7:01	15.2.2022 13:02	2022	5	7
13.1.2022 11:23	7.2.2022 14:46	2022	3	8
13.1.2022 11:19	7.2.2022 14:48	2022	3	9
7.12.2021 7:01	7.2.2022 16:00	2021	50	10
7.12.2021 7:01	7.2.2022 16:00	2021	50	11
30.12.2021 7:01	7.2.2022 12:23	2021	53	12
7.12.2021 11:13	7.2.2022 16:01	2021	50	13
10.1.2022 7:49	10.2.2022 8:31	2022	3	14
10.1.2022 7:50	21.2.2022 9:43	2022	3	15
7.1.2022 7:01	1.3.2022 10:13	2022	2	16
13.1.2022 11:41	7.2.2022 14:52	2022	3	17
10.1.2022 14:51	8.2.2022 15:11	2022	3	18
10.12.2021 12:31	8.2.2022 14:22	2021	50	19
25.1.2022 11:53	8.2.2022 9:01	2022	5	20
12.1.2022 11:21	11.3.2022 11:07	2022	3	21
12.1.2022 11:22	11.3.2022 11:08	2022	3	22
14.12.2021 15:00	8.3.2022 12:22	2021	51	23
12.1.2022 11:20	11.3.2022 11:07	2022	3	24
14.1.2022 12:36	10.2.2022 8:31	2022	3	25
14.1.2022 15:35	15.2.2022 14:48	2022	3	26
14.1.2022 15:37	15.2.2022 14:49	2022	3	27
14.1.2022 15:38	15.2.2022 14:50	2022	3	28
16.11.2021 14:04	9.2.2022 13:16	2021	47	29
17.1.2022 7:47	15.2.2022 13:46	2022	4	30
17.1.2022 7:49	15.2.2022 13:44	2022	4	31
17.1.2022 7:50	15.2.2022 13:43	2022	4	32
2.2.2022 7:01	9.3.2022 10:58	2022	6	33
17.1.2022 15:05	9.3.2022 10:58	2022	4	34
18.1.2022 7:52	11.2.2022 13:29	2022	4	35
19.1.2022 8:01	1.3.2022 12:37	2022	4	36
19.1.2022 8:02	22.2.2022 11:47	2022	4	37
21.1.2022 7:01	23.2.2022 14:47	2022	4	38
27.1.2022 7:01	23.2.2022 15:58	2022	5	39
24.1.2022 13:19	28.2.2022 14:28	2022	5	40
24.1.2022 13:13	17.2.2022 10:39	2022	5	41
25.1.2022 8:00	15.2.2022 13:00	2022	5	42