cancel
Showing results 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

Regular Visitor

Calculated column to classify customers by spending

Hello all

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

 Account Vintage Sales from EP Item 10005281 2019 498 Product 1 10005281 2019 389 Product 2 10005281 2019 881 Product 3 10005281 2019 408 Product 3 10005281 2020 474 Product 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

1 ACCEPTED SOLUTION
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"
)``````

Proud to be a Super User!

2 REPLIES 2
Regular Visitor

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

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

Proud to be a Super User!

Announcements

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 Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors