Forum Discussion

SravaniG's avatar
SravaniG
Icon for Helper I rankHelper I
5 years ago
Solved

How to group customers dynamically based on slicer values

Hi,   i am having a requirement to dynamically group customers.   I will have 3 sclicers Top,Group1,Group2. Based on Top slicer value the TopN customers should display in one table. I could able...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    SravaniG 

    Create a table using the "Enter data" option in the Home ribbon and type in the groups (let's call it 'Grouping Table'):

    Group       Order

    Top 4           1

    Top 5-8.       2

    Top 9-10      3

    (Use the order field to sort the table: select the column Group and use the "Sort column by" option in table view)

    You can use this table as a slicer if need be.

     

    Next create a measure including the grouping values:

    Grouping Values= 

    VAR Top4 = CALCULATE([Revenue], FILTER(Table,  [Rank] < 5))

    VAR Top5to8 = CALCULATE([Revenue], FILTER(Table, [Rank] > 4 && [Rank] < 9))

    VAR Top9to10 = CALCULATE([Revenue], FILTER(Table, [Rank]>8 && [Rank] <11))

    RETURN

    Switch(TRUE(),

    Grouping Table [Group] = "Top 4", Top4,

    Grouping Table[Group] = "Top 5-8", Top5to8,

    Top9to10)

     

    now create a table visual with the Grouping Table[Group] field and the [Grouping Values] measure

     

     

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
5 years ago

SravaniG 

Create a table using the "Enter data" option in the Home ribbon and type in the groups (let's call it 'Grouping Table'):

Group       Order

Top 4           1

Top 5-8.       2

Top 9-10      3

(Use the order field to sort the table: select the column Group and use the "Sort column by" option in table view)

You can use this table as a slicer if need be.

 

Next create a measure including the grouping values:

Grouping Values= 

VAR Top4 = CALCULATE([Revenue], FILTER(Table,  [Rank] < 5))

VAR Top5to8 = CALCULATE([Revenue], FILTER(Table, [Rank] > 4 && [Rank] < 9))

VAR Top9to10 = CALCULATE([Revenue], FILTER(Table, [Rank]>8 && [Rank] <11))

RETURN

Switch(TRUE(),

Grouping Table [Group] = "Top 4", Top4,

Grouping Table[Group] = "Top 5-8", Top5to8,

Top9to10)

 

now create a table visual with the Grouping Table[Group] field and the [Grouping Values] measure

 

 

  • SravaniG's avatar
    SravaniG
    Icon for Helper I rankHelper I
    5 years ago

    Hi,

    Thanks for the reply,

    I have tried this kind of approach only, but Top5to8,Top9to10 values coming wrong.

    The dataset will limit based on Top slicer value. So i will have only top 10 customers, on this top 10 i need to do groups.

    • PaulDBrown's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      5 years ago

      SravaniG 

      Does this work for you?

       

      The Rank Grouping Table is:

       The RANKX measure:

      RANKX Revenue = 
VAR calc = RANKX(ALL(Data), [Sum Revenue], , DESC)
RETURN
IF(ISINSCOPE(Data[Customer ]), calc)

      And the final measure to be used in the table visual:

      Revenue by Rank Group = 
VAR Top4 = CALCULATE([Sum Revenue], FILTER(Data, [RANKX Revenue] <5))
VAR Top5to8 = CALCULATE([Sum Revenue], FILTER(Data, [RANKX Revenue] > 4 && [RANKX Revenue] <9))
VAR Top9to10 = CALCULATE([Sum Revenue], FILTER(Data, [RANKX Revenue] > 8 && [RANKX Revenue] <11))
RETURN
SWITCH(TRUE(),
MAX('Rank Grouping'[Oder]) = 1, Top4,
MAX('Rank Grouping'[Oder]) = 2, Top5to8,
Top9to10)