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 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.
Solved! Go to 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.
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.
Proud to be a Super User!
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.
@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
Proud to be a Super User!
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!
@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
Proud to be a Super User!
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!
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.
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
Proud to be a Super User!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |