Top 10 by Sale for each Category

[Anonymous]

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

[Anonymous]

@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. 

 Link - https://drive.google.com/file/d/1VAAWQnfR7WO6Bajh98OTD6CVR7G87mgQ/view?usp=sharing

Result - Somethig like this, but I would like to have in colunm chart. 

 

Level 07	Product Name	SKU	Sales	On Order	On Transfer	On Hand
Basic Headphones	Skullcandy Jib Wireless - Black	BPADSC000173	$4,478.72	0		148
Basic Headphones	Skullcandy Ink'd Wireless - Black	BPADSC000174	$4,199.16			129
Basic Headphones	Skullcandy Crusher Wireless - Black	BPADSC000176	$3,429.79	0		148
Basic Headphones	Skullcandy Ink'd Wireless - White	BPADSC000175	$2,999.40	0		117
Basic Headphones	Skullcandy Ink'd 2 - Black	BPCASC000856	$2,972.19	0		157
Basic Headphones	Skullcandy Crusher Wireless - White	BPADSC000184	$1,999.90			89
Basic Headphones	Skullcandy Ink'd 2 - Pink/Black	BPADSC000172	$1,532.27	0		180
Basic Headphones	Moshi Mythro Earbuds w/Mic - Rose Pink	BPADAC000116	$539.82			42
Basic Headphones	Moshi Mythro Earbuds w/Mic - Gunmetal Gray	BPCAMT001223	$509.83			41
Basic Headphones	Moshi Mythro Earbuds w/Mic - Jet Silver	BPCAMT001222	$59.98			22
Beats Headphones	Powerbeats3 Wireless Earphones - Black	BPADAP000127	$8,599.57	3		83
Beats Headphones	Beats Solo3 Wireless On-Ear Headphones - Black	BPADAP000119	$8,399.72	3		54
Beats Headphones	Beats Solo3 Wireless On-Ear Headphones - Silver	BPADAP000123	$6,899.77	1		40
Beats Headphones	BeatsX - BLACK	BPADAP000138	$6,299.58	8		65
Beats Headphones	Powerbeats3 Wireless Earphones - Flash Blue	BPADAP000121	$6,199.69	1		64
Beats Headphones	Beats Solo3 Wireless On-Ear Headphones - Rose Gold	BPADAP000122	$5,699.81	0		39
Beats Headphones	BeatsX - WHITE	BPADAP000139	$5,099.66	5		67
Beats Headphones	BeatsX - GREY	BPADAP000140	$3,899.74	5		59
Beats Headphones	Powerbeats3 Wireless Earphones - Shock Yellow	BPADAP000120	$3,799.81	0		54
Beats Headphones	Beats Solo2 Wireless - SPACE GRAY	BNBABD000035	$2,809.76			32
USB-C	Moshi USB-C Multiport Adapter - SILVER	BPPDBK000082	$17,037.87	117		268
USB-C	Moshi USB-C to USB Adapter - SILVER	BPPDAC000055	$8,815.59	184		207
USB-C	Moshi USB-C to HDMI Adapter - SILVER	BPPDAC000085	$4,949.01	32		249
USB-C	Moshi USB-C to Gigabit Ethernet Adapter - SILVER	BPPDAC000084	$3,479.13	21		146
USB-C	Moshi USB-C Charging Cable 2m - SILVER	BPPDAC000087	$2,904.17	4		274
USB-C	Moshi USB-C to VGA Adapter - SILVER	BPPDAC000083	$2,099.40	14		171
USB-C	Griffin Breakaway USB C Mag 6ft Cable	BPPDAC000110	$1,559.61	1		194
USB-C	Moshi USB-C to USB Cable 1m - SILVER	BPPDAC000086	$1,524.39	5		205
USB-C	Native Union Belt Cable USB-C - Slate	BPPDNU000122	$1,199.52	8		112
USB-C	Belkin Thunderbolt Cable USB-C 3ft	BPPDBK000081	$539.82			317

Thank you so much.

[Ashish_Mathur]

Hi @Anonymous,

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

hope this helps.

[Anonymous]

@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 

[Ashish_Mathur]

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.

[Anonymous]

@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 - 

I want something like this, but using my 4 tables on the link.Again, I really appreciate your help. Thank you

[Ashish_Mathur]

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.

[Anonymous]

@Ashish_Mathur

Hello,

I really appreciate your time and help on this matter. Thank you so much for that. I tried above two formulas and It did not work. I have created a file with small quantity of data. You can see it on here (new file with work).

If you have time, can you please tell me, what I have done wrong? I am still trying to get the same result, but my goal is to get in bar chart. so, I can drill down through category level.

Again, thank you so much

[Ashish_Mathur · Accepted solution]

Hi,

See this file.

[Anonymous]

@Ashish_Mathur

Thank you so much for your help and time. This is excatly what I want. Agian, I really apprecite your help. 

[Ashish_Mathur]

You are welcome.

[dexterz]

no need dax,use Facet Key.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380807

[Anonymous]

@dexterz

Thank you for the feed back, my ultimate gaol is to get a stack bar chart.  

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Phil_Seamark]

Hi @Anonymous

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.