Dynamic Grouping via Support table

[Dankang]

 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,

[Dankang]

 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,

[v-shex-msft · Accepted solution]

HI @Dankang,

#1. It is used to ignore result who not suitable for your conditions.(count row with blank table will blank result, power bi will recognize it as zero)

#2. All function is used to break filter effect to let formula calculated on whole table.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

Regards,

Xiaoxin Sheng