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

Uploaded Feb 26, 202517.78 KB

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

Manasi_Panov

Feb 25, 2025

Hello @andyRowan,
I believe this video from DataCrew can be helpful:
https://youtu.be/-yJXArKUNFo?si=Y63nkca8DMZ_dCS8

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…

Beast Mode

Beast Mode

Rank by Sum of Style then Compare Rank to total

Welcome!

Answers

Welcome!

Welcome!

Quick Links

Categories