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
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
Anonymous
Not applicable

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

Top Solution Authors