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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors