Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
4 years ago

Is it possible to dynamically group data based on slicer interactions?

I have a scenario where I need to dynamically display the currently selected item compared to all other items in the data. This is similar to dynamic grouping/binning but is based on a text selection rather than some kind of number.

 

For example, if a user selects a state in a slicer, I want it to dynamically show the GA numbers compared to all other states. If a user then chooses TN, it would show TN compared to all other states.

I've seen various posts online about dynamic grouping/banding with numbers but haven't figured out how to achieve this with text. Anyone have any ideas?

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi arpost,

     

    You need to o have a disconnected table with the states then you need to add two measures

     

    Selected state = CALCULATE (SUM(Table[column]), table[STATE] = SELECTEDVALUE(UNRELATEDTABLE[STATE]))

     

    OTHERS = SUM(Table[column]) - [selected state]

     

    Now used this two measures on your chart. 

     

    Should work has expected,but be aware that this may need some changes if you have other filters or slicers on your report. 

    • arpost's avatar
      arpost
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Thanks for the reply, MFelix! The issue I'm facing is certain visuals don't support the use of multiple measures in the Values field, so I need some kind of Legend value. 😞

       

      For example, the Box and Whisker visual from MAQ only allows a single Value field.

      Do you know of a way to achieve the same result you mentioned but with a Legend-appropriate value?

       

    • arpost's avatar
      arpost
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      That's definitely closer, Jihwan_Kim, but I'm finding that does not translate to my scenario as I can't get it to work. I just blanks unless I select the Other value. I apparently can't attach files here, but here's what would be closer to my scenario with your setup.

       

      Here's a sample of the data:

       

       

      I'd like a user to be able to select a client and see how that client's numbers compare to all other clients excluding that one.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Thank you for your message.

        Do you want to create a Client-Slicer? Or, a State-Slicer?

        The below is for creating a client-slicer.

         

         

        AXIS Client new table = //this new table is used in the visualization, not in the slicer
        UNION ( VALUES ( Data[Client] ), ROW ( "Client", "Others" ) )
        UNION ( VALUES ( Data[Client] ), ROW ( "Client", "Others" ) )
         
        Sales total: =
        SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Slicer Client new table'[Client] ) = "Others", CALCULATE ( SUM ( Data[Sales] ), REMOVEFILTERS () ) - SUM ( Data[Sales] ),
        SELECTEDVALUE ( 'Slicer Client new table'[Client] ) IN VALUES ( Data[Client] ),
        CALCULATE (
        SUM ( Data[Sales] ),
        FILTER (
        Data,
        Data[Client] = SELECTEDVALUE ( 'Slicer Client new table'[Client] )
        )
        ),
        BLANK ()
        )