Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

selected value and others

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

3 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
    AnkitBI
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It worked like magic!! thank you!!!