Forum Discussion

pb202134's avatar
pb202134
Regular Visitor
5 years ago
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 vintages.
An extract from the table for one customer

 

AccountVintageSales from EPItem
100052812019498Product 1
100052812019389Product 2
100052812019881Product 3
100052812019408Product 3
100052812020474Product 1

 

 

I wrote this calculated column to classify each customer based on whether they have bought from both vintages (2019 and 2020) or from one vintage only:

 

 

New or existing to EP = 
var sales2019 = CALCULATE([Sales from EP], 'Summary EP orders'[Vintage]=2019)
var sales2020 = CALCULATE([Sales from EP], 'Summary EP orders'[Vintage]=2020)
return

 if
(sales2019>0 && sales2020>0,"both vintages",
IF(sales2019=0 && sales2020>0,"first time in vintage 20",
IF(sales2019>0 && sales2020=0, " vintage 19, not in 20",
 "never bought")))

 

 

but my formula above does not work, the example customer appears in two types, instead of being classed as "both" vintages

 

what needs amending?

thank you

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    pb202134,

     

    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"
    )

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    5 years ago

    pb202134,

     

    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"
    )