Make ranking according to sales percentage

[Alistar45]

Hi there,

I have quite a situation. I'd like to make a ranking (or clustering) according to the sales percentage of several stores. Is this possible? I already know how to make a ranking in regards to sales amount but i'd like to get a clustering from it. How would i go about it?

Let me show you an example:

CHAIN	STORE	SELL OUT	RANK	SALES SHARE	NEW RANK
CHAIN T	STORE I	$35,975.84	1	30%	S
CHAIN T	STORE D	$19,160.24	2	16%	A
CHAIN T	STORE E	$16,220.86	3	13%	A
CHAIN T	STORE B	$14,516.19	4	12%	A
CHAIN T	STORE H	$13,962.34	5	12%	A
CHAIN T	STORE A	$8,421.58	6	7%	B
CHAIN T	STORE L	$6,244.84	7	5%	B
CHAIN T	STORE F	$2,397.52	8	2%	C
CHAIN T	STORE C	$1,779.78	9	1%	C
CHAIN T	STORE G	$1,449.52	10	1%	C
CHAIN T	STORE J	$881.72	11	1%	C
CHAIN T	STORE K	$388.97	12	0%	C

I have everything until the Sales Share column. Is there any measure to get the NEW RANK column for these stores? If so, how could it be?

Thanks in advance.

[v-eqin-msft]

Hi @Alistar45 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi  @Alistar45 ,

Based on this: Let's say the top 20% gets a letter, middle 50% gets another letter so and so. 

How about trying this:

Measure = 
var _c= COUNTROWS(ALL('Table'))
return  SWITCH(TRUE(), MAX('Table'[RANK])<=_c *0.2 , "A", MAX('Table'[RANK])<=_c *0.5,"B", "C")

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Hariharan_R]

Hi @Alistar45 

Try the below to get the new rank column. You can adjust the range.

New Rank =
SWITCH (
    TRUE (),
    'Table'[SALES SHARE] <= 0.02, "C",
    'Table'[SALES SHARE] <= 0.07, "B",
    'Table'[SALES SHARE] <= 0.12, "A",
    "S"
)

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[Alistar45]

Sorry,

I didn't put a more detailed example of what i want.

For a better view, see this table:

CHAIN	STORE	SELL OUT	RANK	SALES SHARE	NEW RANK
CHAIN T	STORE T	$37,123.00	1	17%	A
CHAIN T	STORE I	$21,589.23	2	10%	A
CHAIN T	STORE D	$19,160.24	3	9%	B
CHAIN T	STORE M	$17,194.46	4	8%	B
CHAIN T	STORE O	$16,825.59	5	8%	B
CHAIN T	STORE E	$16,220.86	6	7%	B
CHAIN T	STORE P	$16,138.42	7	7%	B
CHAIN T	STORE B	$14,516.19	8	7%	B
CHAIN T	STORE H	$13,962.34	9	6%	B
CHAIN T	STORE S	$11,264.14	10	5%	B
CHAIN T	STORE L	$8,764.46	11	4%	C
CHAIN T	STORE A	$8,421.58	12	4%	C
CHAIN T	STORE L	$6,244.84	13	3%	C
CHAIN T	STORE F	$2,397.52	14	1%	C
CHAIN T	STORE Q	$1,888.84	15	1%	C
CHAIN T	STORE C	$1,779.78	16	1%	C
CHAIN T	STORE G	$1,449.52	17	1%	C
CHAIN T	STORE R	$1,140.46	18	1%	C
CHAIN T	STORE N	$1,023.67	19	0%	C
CHAIN T	STORE J	$881.72	20	0%	C
CHAIN T	STORE K	$388.97	21	0%	C

As you can see, the more values i have, the harder it gets to make a condition for every single percentage. Is there a way to make a condition for a group of percentage? Let's say the top 20% gets a letter, middle 50% gets another letter so and so.

Thanks!