cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_wex
Helper I
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:
 
DatePart NumberInvoiced SalesVirtual Summary Rank Based on Summary
1/1/21ABC   $1.001
1/5/21XYZ$5.002
2/5/21ABC   $7.001
4/5/21ZZZ$0.7535
2 REPLIES 2
daxer
Solution Sage
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.

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-...

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.