Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi everybody,
I'm struggling with this task...
I have a table called "Table" with the following columns: Location, Date, Brand, Category, Product Name and Quantity Sold.
I have the following slicers in my Desktop Power BI:
1) "Location" - which allows me to filter by location
2) "Brand" - it allows me to filter ONLY ONE brand
3) "Date" - just a transaction date
4) "Day" - This is a new column that I created and it allows me to filter by certain days of the week
5) "Category" - I can filter by categories of product
The business case is this: When I select a brand in my slicer, I would like to have a pie chart that shows how the brand is positioned and also the other brands for the same category. For example if I select AB brand, I would like to have a pie chart that shows 20% for AB, 15% for CD and 65% for EF (for the date period selected in my slicer)
In case the brand is included in multiple categories, my "Category" slicer should allow me to select the category I want to review. For example if brand AB has products that are in 2 categories such as food and clothing, I would like to filter by food in case I'm not interested in the clothig category (obviously the sum of the pie chart will be 100%).
The column that is used to get the percentage of total sales is "Quantity Sold" and it has the quantity that has been sold for a specific product.
How can I reach the result hoped? I've tried some DAX but the problem is that when I select a brand, the slicer will influence the pie chart.
Thank you in advance for your help!!!
Link to pbix sample
https://drive.google.com/drive/folders/1zyW7zKTpQ15BVhlWHGmVs4IrzvHRkhoh?usp=sharing
Solved! Go to Solution.
Hi @ex8812
I think the problem is not DAX but in which interaction type the brand slicer influences the pie chart. By default, the interaction type is Cross-Filter, so it only shows the selected brand in the pie chart. You can turn off this interaction, then you will see all brands under a category you select. Select brand slicer, click Format tab > Edit interactions, and click None icon on the pie chart like below. See Change how visuals interact in a report - Power BI | Microsoft Docs
Actually I think the best interaction type to meet your need is Cross-Highlight. However, at present slicers cannot influence other visuals with Cross-Highlight type. If you use a table to contain brand values, when you click a brand on the table, you will see the cross-hightlight effect.
Hope this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @ex8812
I think the problem is not DAX but in which interaction type the brand slicer influences the pie chart. By default, the interaction type is Cross-Filter, so it only shows the selected brand in the pie chart. You can turn off this interaction, then you will see all brands under a category you select. Select brand slicer, click Format tab > Edit interactions, and click None icon on the pie chart like below. See Change how visuals interact in a report - Power BI | Microsoft Docs
Actually I think the best interaction type to meet your need is Cross-Highlight. However, at present slicers cannot influence other visuals with Cross-Highlight type. If you use a table to contain brand values, when you click a brand on the table, you will see the cross-hightlight effect.
Hope this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
@ex8812 no problem, this is what I would suggest, throw a sample pbix file showing the expected results, and share it using one drive/google drive. Remove sensitive information before sharing.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks! I've pasted here the link to the pbix file
https://drive.google.com/drive/folders/1zyW7zKTpQ15BVhlWHGmVs4IrzvHRkhoh?usp=sharing
@ex8812 in scenarios like this you have to create a brand table and not have a relationship with any other table, and then use a column from brand table in the slicer, and in measures use this selected brand for the calculations using TREATAS DAX function
✨ Follow us on LinkedIn
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k thank you for your reply! I've never used TREATAS function.. could you help me with this?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |