cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danzidpp Member
Member

Top 10 by Sale for each Category

Hello Guys, 

 

I want to get the top 10 SKU’s by sales for each category Level 07. I have tried suing RANX, but it does not work. I am using two different tables Category (Level 07) and Sales (Products, SKU, Quantity and Sales). Any idea, how to get the top 10 SKU’s by sales for each category on Level 07?

 

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Top 10 by Sale for each Category

Hi,

 

See this file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Phil_Seamark Super Contributor
Super Contributor

Re: Top 10 by Sale for each Category

Hi @danzidpp

 

How would you like the detail to look?  Can you mock up an example?  This can be solved a number of ways but only some might match what you are after.  RankX should work, it's just a case of fitting it to your table structure.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: Top 10 by Sale for each Category

Hi,

 

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danzidpp Member
Member

Re: Top 10 by Sale for each Category

@Ashish_Mathur & @Phil_Seamark

 

Hello Guys, 
 
Thank you so much for the help. This might be complex, 
 
I trying to see my inventory stock for each category which is category Level 07, but I don’t want to see all the product under that category. I only want to see top 10 products by sales for that category. Please see the attached image and sample data. 
 
On my sample data sheet, when I drill down to different pillars it should show top 10 (by sales) for each pillar, but I only got 10 ten for all the product not by category level 07. This is where I got stuck. Any idea will greatly appreciate. 

 

Result - Somethig like this, but I would like to have in colunm chart. 
 
 
Level 07Product NameSKUSalesOn OrderOn TransferOn Hand
Basic HeadphonesSkullcandy Jib Wireless - BlackBPADSC000173$4,478.720 148
Basic HeadphonesSkullcandy Ink'd Wireless - BlackBPADSC000174$4,199.16  129
Basic HeadphonesSkullcandy Crusher Wireless - BlackBPADSC000176$3,429.790 148
Basic HeadphonesSkullcandy Ink'd Wireless - WhiteBPADSC000175$2,999.400 117
Basic HeadphonesSkullcandy Ink'd 2 - BlackBPCASC000856$2,972.190 157
Basic HeadphonesSkullcandy Crusher Wireless - WhiteBPADSC000184$1,999.90  89
Basic HeadphonesSkullcandy Ink'd 2 - Pink/BlackBPADSC000172$1,532.270 180
Basic HeadphonesMoshi Mythro Earbuds w/Mic - Rose PinkBPADAC000116$539.82  42
Basic HeadphonesMoshi Mythro Earbuds w/Mic - Gunmetal GrayBPCAMT001223$509.83  41
Basic HeadphonesMoshi Mythro Earbuds w/Mic - Jet SilverBPCAMT001222$59.98  22
Beats HeadphonesPowerbeats3 Wireless Earphones - BlackBPADAP000127$8,599.573 83
Beats HeadphonesBeats Solo3 Wireless On-Ear Headphones - BlackBPADAP000119$8,399.723 54
Beats HeadphonesBeats Solo3 Wireless On-Ear Headphones - SilverBPADAP000123$6,899.771 40
Beats HeadphonesBeatsX - BLACKBPADAP000138$6,299.588 65
Beats HeadphonesPowerbeats3 Wireless Earphones - Flash BlueBPADAP000121$6,199.691 64
Beats HeadphonesBeats Solo3 Wireless On-Ear Headphones - Rose GoldBPADAP000122$5,699.810 39
Beats HeadphonesBeatsX - WHITEBPADAP000139$5,099.665 67
Beats HeadphonesBeatsX - GREYBPADAP000140$3,899.745 59
Beats HeadphonesPowerbeats3 Wireless Earphones - Shock YellowBPADAP000120$3,799.810 54
Beats HeadphonesBeats Solo2 Wireless - SPACE GRAYBNBABD000035$2,809.76  32
USB-CMoshi USB-C Multiport Adapter - SILVERBPPDBK000082$17,037.87117 268
USB-CMoshi USB-C to USB Adapter - SILVERBPPDAC000055$8,815.59184 207
USB-CMoshi USB-C to HDMI Adapter - SILVERBPPDAC000085$4,949.0132 249
USB-CMoshi USB-C to Gigabit Ethernet Adapter - SILVERBPPDAC000084$3,479.1321 146
USB-CMoshi USB-C Charging Cable 2m - SILVERBPPDAC000087$2,904.174 274
USB-CMoshi USB-C to VGA Adapter - SILVERBPPDAC000083$2,099.4014 171
USB-CGriffin Breakaway USB C Mag 6ft CableBPPDAC000110$1,559.611 194
USB-CMoshi USB-C to USB Cable 1m - SILVERBPPDAC000086$1,524.395 205
USB-CNative Union Belt Cable USB-C - SlateBPPDNU000122$1,199.528 112
USB-CBelkin Thunderbolt Cable USB-C 3ftBPPDBK000081$539.82  317
Thank you so much.
Super User
Super User

Re: Top 10 by Sale for each Category

Hi @danzidpp,

 

You may download my solution from here.  Change the filer crieria in the visual filter section from 5 to 10.

 

hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danzidpp Member
Member

Re: Top 10 by Sale for each Category

@Ashish_Mathur

 

Hello, 

 

Thank you so much, This is exactly what I want, but I run into some issues when I remaking on my side. My power BI version is Version: 2.48.4792.721 64-bit (July, 2017). I think I cannot run some DAX like you. This is the latest version that support to our database.  I am directly connect our vendor database, I have 4 different table as it on sample data. I am having hard time to summarize 4 table in to one. Also, I cannot calculate like you did due version problem I think. Do you know any other way that I can do this. 

 

https://drive.google.com/file/d/1XnUSO6-E2-_bH6PuUFT7pHs10X7jkLxk/view?usp=sharing

 

Thank you so much for your help and time 

Super User
Super User

Re: Top 10 by Sale for each Category

You are welcome.  That looks like a large file.  Please trim the file size down to a couple of MB's.  Also, in your original question, you had only 1 table whereas now you have 4.  On the trimmed file that you share, please describe the question (now that you have 4 tables) and also show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danzidpp Member
Member

Re: Top 10 by Sale for each Category

@Ashish_Mathur

 

Hello, 
 
Again, thank you so much for the help, and I am sorry if I confuse you. This is the same file I uploaded last time. 
 
I have directly connected to the database and I have 4 tables, Category_Dim, Inventory, Sales ACC, Sales Hero and Calendar.  What I am trying to do is get the same result as I mention previously.
 
I want to get the result using this 4 tables. This is what I got stuck. I cannot do the same way as you did on your example, but I want to get the same result using 4 tables. Please see HERE. Its only 6 MB, let me know if you cannot download it. 
 
Result - 
Capture.JPGI want something like this, but using my 4 tables on the link.

 

Again, I really appreciate your help. Thank you
dexterz Regular Visitor
Regular Visitor

Re: Top 10 by Sale for each Category

Super User
Super User

Re: Top 10 by Sale for each Category

Hi,

 

This mistake you are committing is that you are writing Revenue and Rank as calculated column formulas.  They have to be written as measures.  In the Sales ACC table, the measures for Revenue and rank should be should be:

 

Revenue=SUM('Sales ACC'[Sales])

Rank=RANKX(ALL(Category_Dim[SKU]),[Revenue])

 

On the Sales by category tab, drag Level 07 and SKU from the Category_Dim table.  The problem that i am facing is that when i drag Rank to the visual, they system take time to process and after a few minutes returns a message that "there isn't memory to complete the operation"

 

This is probably happening because of the large number of SKU's.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 292 members 3,159 guests
Please welcome our newest community members: