Good Afternoon,
Building a report with dynamic sale ranking (e.g.: 5,10, 25, 50, All). The ranking formula used is:
RankX(all(Sales[Material], Sales[Description]) ,[Total Sales], ,desc )<= RankingSelect)).
This assigns a rank based on total sales by part number. So if top 5 is selected (ranking select) only the top 5 selling items by Part Number/Total Invoiced Sales is displayed. This is good for a summary of the part numbers.
The part that I have been really struggling with creating a cumulative sales line graph based on only the ranked part numbers selected for selected year /previous year. The total for the current year is coming out correct but the cumulative sales by day is off and there are issues with previous years' sales grand total and cumulative detail. I think what is happening is that the ranking on a summary level is breaking down especially with previous year sales.
Trying to come up with a solution and I thought about using the ranking and virtually adding this to sale detail. Don't know if this is even possible or is a good idea at all. The sales detail contains the daily transactions and you can have a part number repeated multiple times. The rank summary is dependent on the current year selected and can change by period selected. My thought is that if I selected the top5 for 2021, for example, then I could virtually add this to a sale detail table and then write another Dax that picks up that ranking in the virtual detail table. If Top 5 is selected then the cumulative sales graph for only those items with a rank LT=5 would be calculated and displayed.
Any thoughts or nudges in the right direction would be great appreciated.
M_Wex
Summary
Part number Rank Total Sales
ABC 1 $8.00
XYZ 2 $5.00
Detail:
| Date | Part Number | Invoiced Sales | Virtual Summary Rank Based on Summary |
| 1/1/21 | ABC | $1.00 | 1 |
| 1/5/21 | XYZ | $5.00 | 2 |
| 2/5/21 | ABC | $7.00 | 1 |
| 4/5/21 | ZZZ | $0.75 | 35 |
