Forum Discussion
Dynamic Grouping via Support table
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
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,