selected value and others

[Anonymous]

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!!!!

[Anonymous]

It worked like magic!! thank you!!!

[dax · Accepted 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.

[AnkitBI]

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.