cancel
Showing results for
Did you mean:
Helper I

## Ranking Summary and Cumulative Sales

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
2 REPLIES 2
Solution Sage

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

Sorry, you cannot add anything to a base table outside the design phase. Base tables are STATIC, which means the numbers in their columns will not change if you start clicking anything that's in a report. And also, it's not possible to help you because one can't write DAX and model data in a vacuum.

Helper I

Thank you daxer. I will put together a sample PBIX file.

Announcements