Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All -- I'm reaching out in need of help as I'm struggling to achieve this:
I have a table of countries, and their respective revenues.
I have a slicer on the countries field to select the countries I want to show, I want to add a pie chart that will show the percentage of the selected client's revenue over the TOTAL revenue. Essentially I'm looking to have the selected countries gas defined in my slicer, the other would be all other clients grouped together. I want this to obviously change depending on my slicer selection
Example:
[Client Name] [Revenue] US 20.3 France 7.4 Germany 17.3
China 25.5
If I select US and France in my slicer, I want my pie chart to show: US, France, Others;
Hopefully somebody can help
Thank you in advance!!!!
Solved! Go to Solution.
Hi ddm123,
You could try below steps:
create table like below
Table 2 = UNION ( VALUES ( pie[Client] ), ROW ( "subname", "others" ) )
Then ceate measure like below
Measure 3 = VAR SelectedSales = CALCULATE ( SUM(pie[Revenue]), INTERSECT ( VALUES ( pie[Client]), VALUES ( 'Table 2'[Client]) ) ) VAR UnSelectedSales = CALCULATE ( SUM(pie[Revenue]), EXCEPT ( ALL ( pie[Client] ), VALUES ( pie[Client] ) ) ) VAR AllSales = CALCULATE ( SUM(pie[Revenue]), ALL ( pie[Client]) ) RETURN IF ( HASONEVALUE ( 'Table 2'[Client] ), SWITCH ( VALUES ( 'Table 2'[Client] ), "others", UnSelectedSales, SelectedSales ), AllSales )
Then create pie chart like below
You could refer to Dynamic Grouping in Power BI using DAX for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It worked like magic!! thank you!!!
Hi ddm123,
You could try below steps:
create table like below
Table 2 = UNION ( VALUES ( pie[Client] ), ROW ( "subname", "others" ) )
Then ceate measure like below
Measure 3 = VAR SelectedSales = CALCULATE ( SUM(pie[Revenue]), INTERSECT ( VALUES ( pie[Client]), VALUES ( 'Table 2'[Client]) ) ) VAR UnSelectedSales = CALCULATE ( SUM(pie[Revenue]), EXCEPT ( ALL ( pie[Client] ), VALUES ( pie[Client] ) ) ) VAR AllSales = CALCULATE ( SUM(pie[Revenue]), ALL ( pie[Client]) ) RETURN IF ( HASONEVALUE ( 'Table 2'[Client] ), SWITCH ( VALUES ( 'Table 2'[Client] ), "others", UnSelectedSales, SelectedSales ), AllSales )
Then create pie chart like below
You could refer to Dynamic Grouping in Power BI using DAX for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It is not possible to show 'Others' dynamically based on Selection Value. I have tried a WorkAround, only problem being User has to select Others(can give meaningful Name) also in Slicer, which I don't think is desirable in all cases.
Had a good and fun learning trying this out 🙂
1) Added a Row in Power Query using below.
= Table.InsertRows(#"Renamed Columns",Table.RowCount(#"Renamed Columns"),{[Client Name = "Other",Revenue = 0]})
2) Createad a New measure as below.
Revenue_Value = // Get All Selected Vlaues. Others needs to be part of it var SelectedValues = ALLSELECTED(Source[Client Name]) // Calculate Sum of Revenue for Client Names not in Selected List var OtherSum = calculate(sum(Source[Revenue]),(filter(all(Source[Client Name]),NOT(Source[Client Name] IN SelectedValues)))) // On Visualisataion, if others then show OtherSum else normal sum var Finalvalue = if(max(Source[Client Name]) = "Other",OtherSum,sum(Source[Revenue])) Return Finalvalue
3) Put Client Name and Measure on any visualisation, you will get desired result. Again, it will only work if Other is selected in Slicer.
Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do like the response if it seems good and helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |