cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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.

181723_01.png

I’ve found “Data categorization and labeling based on date range” but can’t apply that to what I want.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Categorize and label data based on a range

@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]))


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Categorize and label data based on a range

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
Not applicable

Re: Categorize and label data based on a range

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

Many thanks, Lydia

6 REPLIES 6
Moderator v-yuezhe-msft
Moderator

Re: Categorize and label data based on a range

@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]))


1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Categorize and label data based on a range

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
Not applicable

Re: Categorize and label data based on a range

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

Many thanks, Lydia

Anonymous
Not applicable

Re: Categorize and label data based on a range

Hi Ashish_Mathur,

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

Many thanks, Ashish!

Anonymous
Not applicable

Re: Categorize and label data based on a range

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

/EJ

Super User
Super User

Re: Categorize and label data based on a range

You are welcome.