Forum Discussion
Solved
Calculated column to classify customers by spending
Hello all Could you please help, there is an issue with the caclulcated column i created . I have a table with customers and the vintage from the products they have bought from two different vinta...
Your calculated column was missing ALLEXCEPT. Also, I replaced the IF statements with SWITCH (simpler). Try this:
New or existing to EP = VAR sales2019 = CALCULATE ( [Sales from EP], 'Summary EP orders'[Vintage] = 2019, ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] ) ) VAR sales2020 = CALCULATE ( [Sales from EP], 'Summary EP orders'[Vintage] = 2020, ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] ) ) RETURN SWITCH ( TRUE (), sales2019 > 0 && sales2020 > 0, "both vintages", sales2019 = 0 && sales2020 > 0, "first time in vintage 20", sales2019 > 0 && sales2020 = 0, " vintage 19, not in 20", "never bought" )
DataInsights
Super User
Super User
Your calculated column was missing ALLEXCEPT. Also, I replaced the IF statements with SWITCH (simpler). Try this:
New or existing to EP =
VAR sales2019 =
CALCULATE (
[Sales from EP],
'Summary EP orders'[Vintage] = 2019,
ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] )
)
VAR sales2020 =
CALCULATE (
[Sales from EP],
'Summary EP orders'[Vintage] = 2020,
ALLEXCEPT ( 'Summary EP orders', 'Summary EP orders'[Account] )
)
RETURN
SWITCH (
TRUE (),
sales2019 > 0
&& sales2020 > 0, "both vintages",
sales2019 = 0
&& sales2020 > 0, "first time in vintage 20",
sales2019 > 0
&& sales2020 = 0, " vintage 19, not in 20",
"never bought"
)