Ranking for Sales Transactions Listing

[RSSAPowerBI]

Hello,

I currently have a sales listing (indicating all sales transactions for 2023 and 2024).

This listing includes the date, product name, sales amount and the sales channel.

For example:

Date:           | Product      | Sales amount | Sales channel

01/01/2024 | Cooldrink   | $100              | Cafe

02/01/2024 | Cap            | $200               | Gift shop

05/06/2024 | Cooldrink  | $150               | Gift shop

07/07/2024 | Chips         | $50                 | Cafe

I want to visualise the products and their total sales as well as the proportion of the sales channel. Then have the abillity to rank the products in order to filter on top 10, bottom 10 etc. [Biggest issue is that some products are sold in more than one channel. Thus I can not just create a new table for total sales]

It is important that I if I filtered on Cooldrink (top seller), that I could see it had a 50% Cafe and 50% Gift Shop proportion.

[Anonymous · Accepted solution]

Hi, @RSSAPowerBI 

The use of pie charts can react to the problem of occupancy, and you can try to solve your problem using the following methods.

Varies with Slicer's choice:

Sales Except Product = 
CALCULATE(SUM('Table'[Sales amount]),ALLEXCEPT('Table','Table'[Product]))

Rank = RANKX(ALLSELECTED('Table'),[Sales Except Product],,DESC,Dense)

Sales = 
VAR _rankTop10 = RANKX(ALLSELECTED('Table'),[Sales except product],,DESC,Dense)
VAR _rankBottom10 = RANKX(ALLSELECTED('Table'),[Sales except product],,ASC,Dense)
VAR _allsales = CALCULATE(SUM('Table'[Sales amount]),ALL())
VAR _proportions = DIVIDE([Sales except product],_allsales)
VAR _slicer = SELECTEDVALUE(Slicer[RankSales])
VAR _result = SWITCH(
    _slicer,
    "Top 10",IF(_rankTop10<=10,_proportions),
    "Bottom 10",IF(_rankBottom10<=10,_proportions),
    _proportions
)
RETURN
_result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[suparnababu8]

Hi   @RSSAPowerBI 

Please follow below steps 

• create a total sales measure -  Total Sales = SUM('Sales'[Sales amount])
• Then create sales channel proporation measure by using below code  
  Sales Channel Proportion =
  DIVIDE(
  SUM('Sales'[Sales amount]),
  CALCULATE(SUM('Sales'[Sales amount]), ALLEXCEPT('Sales', 'Sales'[Product])))​
• Create Rank measure . Product Rank = RANKX(ALL('Sales'[Product]), [Total Sales], , DESC, Dense)
• Drag the filed as shown in below image. 
   

  
  
  

  If you are looking for the same. Then follow these steps.

  Thanks