Categorize and label data based on a range

[Anonymous]

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 · Accepted solution]

@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

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

Thank you both for the solutions. This will help me furhter in understanding the matter.

/EJ

[Ashish_Mathur · Accepted solution]

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.

[TheBishopEmpire]

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. 

[Ashish_Mathur]

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]

Hi Ashish_Mathur,

Works! Now I need to find out why it works.

Many thanks, Ashish!

[Ashish_Mathur]

You are welcome.

[Anonymous · Accepted solution]

@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

[Anonymous · Accepted solution]

Works! Now I need to find out why it works.

Many thanks, Lydia