Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

TOP 25 Products Sold with a Single Product

I am trying to figure out a dax formula that will return table of Top 25 Products sold with a Single Product. 

 

The idea is to be able to Drill down on a product to a page which would display a table of 25 products most commonly sold with the Product that is selected. 

 

I have a large data set of the past 3 years with ProductID, SalesDocID, and Qty sold per Sales Doc. I am thinking is a combination of TOPN and CountX or something but I am new to DAX so I am not really sure

 

Here is a sample data set

Product	Sales document	Qty
6507968	1439115	1
6507737	1491663	1
6507750	1491663	1
6500568	1562306	3
6506385	1562306	2
6506386	1562306	1
6506757	1562306	46
6507071	1562306	2
6507073	1562306	1
6508529	1562306	5
6508532	1562306	6
6508535	1562306	7
6506016	1583126	1
6500564	1650179	1
6502416	1650179	3
6502480	1650179	19
6500184	1689253	1
3001562	1696959	1
3001563	1697714	1
3001563	1706018	1
3001563	1712570	1
3001563	1714391	1
3001562	1725230	1
3001563	1725242	1
3001562	1729304	1
3001562	1760809	2
3001564	1760809	2
6501606	1775800	7
6501610	1775800	53
6501618	1775800	10
6501630	1775800	10
6501634	1775800	82
6504490	1775800	41
6504494	1775800	3
6501610	1780738	6
6501622	1780738	3
6501634	1780738	16
6504490	1780738	2
3001563	1791050	1
6506195	1792263	0
6507397	1792263	0
6507508	1792263	0
6507781	1792263	0
3001563	1799425	1
3001563	1801879	1
3001562	1804318	1
3001562	1804516	1
3 REPLIES 3
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

You could take a look at this post, it has the same logic. 

https://community.powerbi.com/t5/Desktop/Indicate-if-among-Top-X-values/m-p/628827#M300474

https://community.powerbi.com/t5/Desktop/How-to-show-top-50-and-bottom-50-records-in-a-table/m-p/627...

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I beleive that I do have to use some evaluation with RANKX but how do I know provide the context of the Sales Order. 

 

You example only has ID and QTY, which only provides me with the TOP and BOTTOM performing ID's. I want to know Of all the sales orders what other products are frequently sold with the ID I have already filtered on the page

hi, @Anonymous 

This depends on the logic of your rank measure

You may try this formula

rank = RANKX(ALLSELECTED('Table'[Product]),CALCULATE(SUM('Table'[Qty])),,DESC)

 

 

Then use this measure as a visual level filter to filter data

If not your case, please share your expected output of the sample data.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.