Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RSSAPowerBI
New Member

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.

1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1737615563293.png

vyaningymsft_2-1737618595698.png

Varies with Slicer's choice:

vyaningymsft_3-1737618611573.png

 

 

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

View solution in original post

2 REPLIES 2
v-yaningy-msft
Community Support
Community Support

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.

vyaningymsft_0-1737615563293.png

vyaningymsft_2-1737618595698.png

Varies with Slicer's choice:

vyaningymsft_3-1737618611573.png

 

 

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
Super User
Super User

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. 
     

      

     

    suparnababu8_1-1737616889454.png

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

     

    Thanks

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.