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 ...
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,

     

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,

       

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

        Thank you so much, that gets me what I need. 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      Share some data and show the expected result.