Forum Discussion
Categorize and label data based on a range
Hi,
I would like to categorize customers by their annual revenue using a definition in a separate table.
I assume it’s best done with a calculated column.
I use following formula now.
=IF(AllAccounts[Revenue of customer group]<10000;"Portfolio";
IF(AllAccounts[Revenue of customer group]<100000;"Account";
IF(AllAccounts[Revenue of customer group]<200000;"Strategic-";"Strategic+")))
I prefer to relate to a table with the definition as this allows for changing the definitions in a later stage in an easier way.
I’ve found “Data categorization and labeling based on date range” but can’t apply that to what I want.
Anonymous,
Create the AccountType column using DAX below in the AllAccounts table.Account Type = CALCULATE(FIRSTNONBLANK(relatetable[Account type],relatetable[Account type]),FILTER(relatetable,relatetable[Lower cut-off]<=AllAccounts[Revenue of customer group]&&relatetable[Upper cut-off]>=AllAccounts[Revenue of customer group]))
Regards,
LydiaHi,
Write this calculated column formula in the Data Table
=CALCULATE(FIRSTNONBLANK(limits[Account Type],1),FILTER(limits,limits[Lower cut-off]<=EARLIER(Data[Revenue of Customer Group])&&limits[Upper cut-off]>=EARLIER(Data[Revenue of Customer Group])))
Hope this helps.
8 Replies
Hi,
Write this calculated column formula in the Data Table
=CALCULATE(FIRSTNONBLANK(limits[Account Type],1),FILTER(limits,limits[Lower cut-off]<=EARLIER(Data[Revenue of Customer Group])&&limits[Upper cut-off]>=EARLIER(Data[Revenue of Customer Group])))
Hope this helps.
You are welcome.
This DAX formula worked for me. Ashish, could you explain the formula a bit further so that I can understand how the "EARLIER" functions work in this case?
Thanks in advance.You are welcome. The EARLIER() function can be understood as the value in the current row of the column specified in the EARLIER() function.
Anonymous,
Create the AccountType column using DAX below in the AllAccounts table.Account Type = CALCULATE(FIRSTNONBLANK(relatetable[Account type],relatetable[Account type]),FILTER(relatetable,relatetable[Lower cut-off]<=AllAccounts[Revenue of customer group]&&relatetable[Upper cut-off]>=AllAccounts[Revenue of customer group]))
Regards,
Lydia
