Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pb202134
Regular Visitor

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

pb202134_0-1626337496773.png

 

what needs amending?

thank you

 

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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

 

DataInsights_0-1626390661833.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
pb202134
Regular Visitor

Thank you @DataInsights . This is working perfectly fine now!

DataInsights
Super User
Super User

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

 

DataInsights_0-1626390661833.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.