March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello Power Bi masters,
I'm a student worker trying build power Bi reports in finance.
In my report I use multi level slicer for:
Level 1 - Regions
Level 2 - Countries
One of many visuals there is a donut-chart showing the country share on the region revenue. This works well if I select the region in the slicer. But when I just the country the donut-chart shows just the country so 100%.
What I want to have is when I select any kind of country in level 2 the donut-chart ignores this and shows the whole region where the country belongs to. (level1)
Bellow I attach pictures - on the second picture I want to see the same chart as on the first one.
Could you please somebody recommend me a solution?
Thank you in advance,
Dominik
@Anonymous If it's just for this pie chart, you can use 'Edit Interactions' to turn off the interaction between slicer and the pie chart. So select the slicer ,click Format tab > Edit Interactions. Click the circle with line through it at top of pie chart. Click Edit interactions again to turn off these buttons. https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hello Alisson,
Thank you for your effort but I need the chart to interact with the slicer but just the first level of it. I need it to react on selecting BU lvl but not on SBU lvl.
Thanks and regards,
Dominik
Hi @Anonymous ,
You can create a measure as below and apply it on your Donut chart. Please check whether that is what you want.
Best Regards
Hi Yingyinr,
Thank you for your reply and I also very apreciate you created the table with my namings.
unfortunately it's not the solution to my problem yet.
After selecting the SBU Poland I would like to see the exact same chart as the whole BU would be selected.
So after selecting SBU Poland the donat chart should still show not just the share of SBU Poland on the whole BU but also Ukraines' and Baltics' shares.
Do you have any Idea how to do that?
Thank you,
Dominik
Hi @Anonymous ,
If apply the region and country field in the fact table to the slicer, then only the value of the selected country will be displayed in the Dotnut chart when you select a specific country. Because it is affected by the slicer selections. Therefore, it may be necessary to create another dimension table (including region and countryfield) and apply these two fields in dimension table to the slicer. I updated my sample pbix file(see attachment), please check if it is what you want.
1. Create a dimension table
RegionCountry = SUMMARIZECOLUMNS('Sales'[Region],'Sales'[Country])
2. Create a measure as below
Measure =
CALCULATE (
SUM ( 'Sales'[Sales] ),
FILTER ( 'Sales', 'Sales'[Region] IN ALLSELECTED ( 'RegionCountry'[Region] ) )
)
Best Regards
Hello @v-yiruan-msft ,
Thank you very much for takeing the time an creating the file. It looks we are on the right track here. However I tried to aply this to my dataset and it doesn't work but I think it's just a detail we can solve.
1st problem: I can not create new dimension table because I'm using a dataset from our global BI department so I can not edit old or create new tables. I can just add measures in my file.
But the dataset contains something that looks like what you have created.
The sales table has a collumn Profit center and profit center is the linking collumn in the table for countries/regions hierarchy. I'm adding the picture of it.
I tried to modify your measure using profit centres instead of region but it doesnt work.
The problem can be also in: you are using in the category of chart the country from sales table. But I can not do that I have to use the SBU/country from the Profit center table.
Idk if I explained it well now - Hope yes and we can push it to the end.
Thanks angain,
Dominik
Do you have a measure for GR share? You can update it using something like ALLSELECTED(Region) to show all the values for that region?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison, unfortunately it doesnt solve the issue, it's still showing the same chart.
Anyway thank you for taking your time and trying to help me.
Dominik
@Anonymous , Try a measure like
measure =
var _reg = allselected(Geo[regions])
return
calculate([Measure], removefilters(Geo[country]) , filter(geo,Geo[regions] in _reg))
Hello Amitchandak,
thank you for trying to help me, I tried to use you advice but the chart still shows just the one country as you can see bellow.
Thank you again,
Dominik
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
86 | |
73 | |
58 | |
52 |
User | Count |
---|---|
197 | |
133 | |
107 | |
69 | |
65 |