Convert Sales to Percentage

[jmarleigh]

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

[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

[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 · Accepted solution]

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,

NJ

[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

[mborriero]

You can just open the file with a text editor and replace 11.3 with 11.0.