Forum Discussion

RSSAPowerBI's avatar
RSSAPowerBI
New Member
1 year ago
Solved

Ranking for Sales Transactions Listing

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.

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
    suparnababu8
    Icon for Super User rankSuper User
    1 year ago

    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