Create Custom Groups based on Spend

[Anonymous]

Hi all,

I am trying to create custom groups based on customer spend per year, please see image. Is there a way to dynamically do this.

[Nathaniel_C]

Hi @Anonymous ,

Have you tried using a SWITCH?

Let me know if you have any questions. https://docs.microsoft.com/en-us/dax/switch-function-dax

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

SWITCH(
               TRUE(),
                table [Net Spend] >20000,"Champions",
                table [Net Spend] >10000, "Winners")

note: The SWITCH() stops as soon as something is true, therefore if the [Net Spend] is greater than 20k, it stops there an returns Champions.

[Anonymous]

Hi @Nathaniel_C , thanks for the answer. This doesn't seem to be working. Please see attached. I am struggling to have only those customers each year who spend the amount in certain tier, switch is calculating something different.

[Nathaniel_C]

@Anonymous ,

So, it has to be a certain threshold that is met every year, and if not met in any year, then they don't qualifiy? So one year, they could be a champion, but the next year they don't spend enough then they drop off regardless of how much they spent in the past? If that is true, we could do a test on each year. An easy way is to assign a number to each level, say a champion is 5, and then it goes down from there 4 3 2 1 0.  Then we could simply get the min from all their year numbers. Does sound right?

BTW I cannot tell what the picture is showing me...the arrow means what?  When you post online, there are so many chances to misunderstand, it is good to really spell it out.  I know you are trying to get this done as quickly as possible, but we are not paid for this, so make it easy on us.

Thank you.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hi @Nathaniel_C ,

Sorry for the confusion, it is exactly what you mentioned. It has to be a certain threshold that is met each year. The customers can be 50k+ 1 year and could be 10k+ the next year. In the picture above what I wanted to communicate was that those customers should be in the tier above because they had more than 10k in sales that year.

Here is what I want, lets assume customer A had 10k+ sales in 2018 and 1k-5k sales in 2019, I want customer A to populate in both 10k+ tier with 2019 as 0 and 1k-2k tier with 2018 as 0.

let me know if this makes sense.

Thanks!

[Nathaniel_C]

@Anonymous ,

Thank you for your response. Would you also supply us with an expected outcome.  You could dummy it up in Excel if you want.  That gives the target.

Thanks,

Nathaniel

[Anonymous]

Hi @Nathaniel_C ,

My data is structured as below with multiple rows of sales by each year:

Customer	Net Sales	Year
A	5000	2018
B	2000	2019
C	300	2019
D	4000	2018
E	200	2018
F	10000	2019
A	50000	2019
B	100	2018
C	10000	2018
D	8000	2019
E	50000	2019
F	100	2018
A	200	2019
B	5000	2018
C	50000	2019
D	5000	2019

I need my output to be drilldown of the groups based on spend for example if customer A spent 5000 in 2018 and 50200 in 2019 it will be populated under different tiers of 0-10k for 2018 and 10k+ for 2019 as below

Customer Bucket		2018	2019
10k+	A		50200
	C	10000	50300
	D		13000
	E		50000
	F		10000
0-10K	A	5000
	B	5100	2000
	D	4000
	E	200
	F	100

Let me know if this makes sense, thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Is this what you want?

If so, you can create your column and measure like so:

column:

Customer Bucket = 
VAR sum_sales =
    CALCULATE (
        SUM ( 'Table'[Net Sales] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer], 'Table'[Year] )
    )
RETURN
    IF ( sum_sales < 10000, "0-10K", IF ( sum_sales >= 10000, "10K+", BLANK () ) )

Measure:

Sum sales of each Customer = SUM ( 'Table'[Net Sales] )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey ,

Thanks for this, this is great 🙂

[Nathaniel_C]

Hi @Anonymous ,

Tell me if this works for you. Which bucket?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel