Forum Discussion

Sha's avatar
Sha
Icon for Helper II rankHelper II
6 years ago
Solved

How to get common data from selected value in slicer

 I have one table that contains business unit, client, sales.  I am trying to figure out if my selected business unit (slicer) shares the same client with other business units and get total of sales by those shared clients.  Any help would be appreciated.

 

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Sha ,

     

    First of all, we can create a separate table to show the data, then we can use a measure in visual filter to filter it:

     

    TableToCompare = 'Table'

     

    HasSameClient = 
IF (
    AND (
        SELECTEDVALUE ( 'TableToCompare'[Client] ) IN DISTINCT ( 'Table'[Client] ),
        NOT SELECTEDVALUE ( TableToCompare[Bus unit] ) IN FILTERS ( 'Table'[Bus unit] )
    ),
    1,
    -1
)

     

     

     

    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Sha ,

     

    We can use the variable to optimise the code, if you have any other questions, please kindly ask here and we will try to resolve it.

     

    # of Bus Units =
VAR SelectedBus =
    SELECTEDVALUE ( 'Table'[Bus unit] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'TableToCompare'[Bus unit] ),
        'TableToCompare'[Bus unit] <> SelectedBus
    )

     


    Best regards,

     

12 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    hi Sha 

    it could be smth like measure

    Measure = 
CALCULATE(
SUM('Table1'[Sales]);
FILTER(ALL('Table1';'Table1'[Client]=SELECTEDVALUE('Table1'[Client]) && 'Table1'[Business Unit]<>SELECTEDVALUE('Table1'[Business Unit]))
)

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Sha's avatar
      Sha
      Icon for Helper II rankHelper II
      6 years ago

      That didn't work, it just returns my selected business unit's sales.  I need to get all the Clients that also have sales in other business units.  Expecting to see something like this.  If I'm comparing to Bus unit 3, I would see something like this...

      Client       Bus unit                      Sales

      Name1    Bus Unit1                  $xxx

      Name1    Bus Unit2                  $xxx

      Name1    Bus Unit4                  $xxx

      Name2    Bus Unit4                  $xxx

      Name2    Bus Unit4                  $xxx

       

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Sha ,

         

        First of all, we can create a separate table to show the data, then we can use a measure in visual filter to filter it:

         

        TableToCompare = 'Table'

         

        HasSameClient = 
IF (
    AND (
        SELECTEDVALUE ( 'TableToCompare'[Client] ) IN DISTINCT ( 'Table'[Client] ),
        NOT SELECTEDVALUE ( TableToCompare[Bus unit] ) IN FILTERS ( 'Table'[Bus unit] )
    ),
    1,
    -1
)

         

         

         

        If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.


        Best regards,