Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
Thank you both for the solutions. This will help me furhter in understanding the matter.
/EJ
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.
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.
Hi Ashish_Mathur,
Works! Now I need to find out why it works.
Many thanks, Ashish!
You are welcome.
@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
Works! Now I need to find out why it works.
Many thanks, Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |