Forum Discussion

Dankang's avatar
Dankang
Regular Visitor
8 years ago
Solved

Dynamic Grouping via Support table

 Hi Guys,

 

Hope you all had anice weekend.

 

I am currently learning how to make dynamic grouping via support table.

 

Customer Sales by Group =
CALCULATE( [Total Sales],
FILTER( VALUES( Customers[Customer Names] ),
COUNTROWS(
FILTER( 'Customer Groups',
RANKX( ALL( Customers[Customer Names] ), [Total Sales],,DESC ) > 'Customer Groups'[Min]
&& RANKX( ALL( Customers[Customer Names] ), [Total Sales],, DESC ) <= 'Customer Groups'[Max] ) )
> 0 ))

 

here is the fomula that i just learnt. So basically i created a new table that has 'Top 5', '5 to 20' and 'the rest' that evaulates the groups.

 

2 questions.

1. Why does there need be countrows >0 ? does the fomula not work if i dont have the countrows function there?

2. Why do i need all[customer names] in RANKX? if i remove all filters for Customers names by using 'ALL' fuction the total sales returns the same and i won't be able to rank customers by 'Sales Total'?

 

Thanks in advance for your help.

 

Cheers,

2 Replies

  • Dankang's avatar
    Dankang
    Regular Visitor

     Hi Guys,

     

    Hope you all had a nice weekend.

     

    I am currently learning how to make dynamic grouping via support table.

     

    Customer Sales by Group =
    CALCULATE( [Total Sales],
    FILTER( VALUES( Customers[Customer Names] ),
    COUNTROWS(
    FILTER( 'Customer Groups',
    RANKX( ALL( Customers[Customer Names] ), [Total Sales],,DESC ) > 'Customer Groups'[Min]
    && RANKX( ALL( Customers[Customer Names] ), [Total Sales],, DESC ) <= 'Customer Groups'[Max] ) )
    > 0 ))

     

    here is the fomula that i just learnt. So basically i created a new table that has 'Top 5', '5 to 20' and 'the rest' that evaulates the groups.

     

    2 questions.

    1. Why does there need be 'countrows >0' ? does the fomula not work if i dont have the countrows function there?

    2. Why do i need all[customer names] in RANKX? if i remove all filters for Customers names by using 'ALL' fuction the total sales returns the same and i won't be able to rank customers by 'Sales Total'?

     

    Thanks in advance for your help.

     

    Cheers,