Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
Best Regards,
Lin
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
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |