Hi, I have a situation where I'm trying to convert the sales units at a store/sku level to a percentage of the sales at the highest selling store and then for every 17% attribute an A through F grade. I'm just wondering how I would go about doing this? The ranking would look something like below: Percent Rank Range Upper Limit Lower Limit Store Category 100.0% 83.9% A 83.8% 67.4% B 67.3% 50.8% C 50.7% 34.2% D 34.1% 17.1% E 17.0% 0.0% F And the data that I'm using would be setup like below and I would be wanting to calculate the final two columns. StoreSKUSalesPercentage of Total SalesRank104310014100%A10441001375%B10451001250%D104310024100%A10441002375%B10451002250%D Thanks, Justin

Convert Sales to Percentage

Hi,

I have a situation where I'm trying to convert the sales units at a store/sku level to a percentage of the sales at the highest selling store and then for every 17% attribute an A through F grade.

I'm just wondering how I would go about doing this?

The ranking would look something like below:

Percent Rank Range
Upper Limit	Lower Limit	Store Category
100.0%	83.9%	A
83.8%	67.4%	B
67.3%	50.8%	C
50.7%	34.2%	D
34.1%	17.1%	E
17.0%	0.0%	F

And the data that I'm using would be setup like below and I would be wanting to calculate the final two columns.

Store	SKU	Sales	Percentage of Total Sales	Rank
1043	1001	4	100%	A
1044	1001	3	75%	B
1045	1001	2	50%	D
1043	1002	4	100%	A
1044	1002	3	75%	B
1045	1002	2	50%	D

Thanks,

Justin

Expression

Accepted answers

NicoleJohnson

Alternative solution (because it wouldn't be a normal day in the Alteryx Community if @MarqueeCrew and I didn't reply to all the same things at almost the same time! haha)

This method would allow a bit more of a dynamic assignment of the % cutoffs (rather than manual entry in the tile tool) by updating the input that contains the limit parameters. Used the Append tool to append the Upper & Lower limits to the data, then filtered to show only those lines that fell within the limits. Also included the calculations for getting the %'s.

Hope these help!

Cheers,

ConvertSalesToPercentage.yxmd

All comments

MarqueeCrew

Attached is a workflow solution that uses a TILE (manual) to achieve your results. Please try it and see if it solves your challenge for you.

Cheers,

Mark

Convert sales to percentage.yxmd

jmarleigh

Hi,

Thanks for the file, but because you're using a newer version of Alteryx I'm unable to open it. Any chance you're able to save it for older version? I'm using 11.0.6.28907.

Thanks,

Justin

NicoleJohnson

Alternative solution (because it wouldn't be a normal day in the Alteryx Community if @MarqueeCrew and I didn't reply to all the same things at almost the same time! haha)

Hope these help!

Cheers,

ConvertSalesToPercentage.yxmd

mborriero

@MarqueeCrewI love your solution. It is always good to see all the different approach to the same use case.

I would have just used a formula tool with an IF statement.

if [Percentage of Total Sales] <= 1 and [Percentage of Total Sales] >= 0.839 then "A"
elseif [Percentage of Total Sales] <= 0.838 and [Percentage of Total Sales] >= 0.674 then "B"
elseif [Percentage of Total Sales] <= 0.673 and [Percentage of Total Sales] >= 0.508 then "C"
elseif [Percentage of Total Sales] <= 0.507 and [Percentage of Total Sales] >= 0.342 then "D"
elseif [Percentage of Total Sales] <= 0.341 and [Percentage of Total Sales] >= 0.171 then "E"
elseif [Percentage of Total Sales] <= 0.170 and [Percentage of Total Sales] >= 0.0 then "F"
else ""
endif

Convert sales to percentage.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335