Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
Hi @RSSAPowerBI
Please follow below steps
If you are looking for the same. Then follow these steps.
Thanks
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |