Rank by Sum of Style then Compare Rank to total

andyRowan · February 25

Hi,

I have transactional data over periods of time. Data includes dates and time periods as well as SKUs and Net sales. I want to be able to dynamically rank SKUs based on net sales to create a top 10 group and then compare that group to the total sales for the category.

Trying this but I don't want the SKUs in my report

CASE WHEN RANK() OVER (PARTITION BY SKU USE ME ORDER BY SUM(Net Sales) DESC) <= 10 THEN 'Top 10'
ELSE 'Balance'
END

I want to create a group of top 10 and not top 10 and then compare to total for that week but be able to filter for the year and have it adjust.

Manasi_Panov · February 25

Hello @andyRowan,

I believe this video from DataCrew can be helpful:

https://youtu.be/-yJXArKUNFo?si=Y63nkca8DMZ_dCS8

JasonAltenburg · February 26

Hello @andyRowan

Can you describe or sketch out briefly in Excel the output you are looking to achieve in Domo with this?

In other words, are you looking to make a table card to do the comparison, or use these metrics in a different type of chart like a line or bar chart?

andyRowan · February 26

Rank Sample.xlsx

So my data is essentially structured like columns A-C. I want to rank by the sum of sales by style. Then I want to show the group that it's in and that group's percent to total.

andyRowan · February 26

https://community-forums.domo.com/main/discussion/comment/100793#Comment_100793

I still need the totals from the full dataset so unfortunately this isn't going to work for the use case.

Manasi_Panov · February 27

Hi @andyRowan,

I'm still trying to find a way to make the final calculation but this is what I got until now:

Rank
RANK() OVER (ORDER BY SUM(Sales) DESC)

Total
SUM(SUM(Sales) FIXED ())

isTop10
CASE
WHEN RANK() OVER (ORDER BY SUM(Sales) DESC) < 11 THEN 'Top 10'
ELSE 'Other'
END

I still can't find a way to calculate the Total of the Top 10. If someone has a better idea…

Rank by Sum of Style then Compare Rank to total

Answers

Categories