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

HOW MANY CUSTOMERS WE'VE LOST THIS YEAR?

I need your help to answer the question highlighted above.

 

WHAT THE DATA LOOKS LIKE (Tabular Model, Dynamic Live Connection)

- Fact table: SELLINGS (Product, Customer, Amount, Price, …)

- Dimension Table: CUSTOMERS (id, name, …)

 

DESIDERED CUSTOMERS SEGMENTATION

 

LABELIDENTIFIES
-100

Lost customer: the year before was the last time he bought

-1

Descreased customer: the year before he bought more than now

1

Increased customer: the year before he bought less than now

100

Gained customer: this year was the first time he has bought

 

WHAT I’VE DONE SO FAR

 

I’ve created a measure as follows to calculated the segmentation for each customers (this formula gives the desidered output):

 

segmentation =

VAR maxyear = max(SELLINGS[year])

var sellings = round(CALCULATE(sum(SELLINGS[price]), SELLINGS[year] = maxyear), 2)

var sellingsBefore = round(CALCULATE(sum(SELLINGS[price]), SELLINGS[year] = maxyear -1), 2)

 

return if(and(sellingsBefore <> 0, sellings <= 0), -100, if(and(sellingsBefore = 0, f sellings > 0), 100, if(sellings > sellingsBefore, 1, if(sellings < sellingsBefore, -1, 0))))

 

And tried the following formula to calculate the lost customers:

 

lostCustomers = CALCULATE(DISTINCTCOUNT(CUSTOMERS[id]), FILTER ( SELLINGS, SELLINGS[segmentation] = -100))

 

The value returned is blank but I know the lost customer are 37.

 

WHAT I GUESS

 

I guess my formula is wrong because the formula is not applied to the customer id before the distinct count is applied. The problem is I don’t know how to fix it. How to build a matrix style table on which I can perform the distinct count?

 

CAN YOU HELP ME?

How can I count customers and show in a graph how many for each segmentation? Thank you!

1 ACCEPTED SOLUTION

Playing with the formula you provided and changing it I've came to the desidered solution:

 

Lost Customers =
VAR Year = MAX(fatturato[anno_fattura])
Return
CALCULATE(
COUNTROWS( FILTER (
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)) ,
"Year-1" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1))) ,

[Year] <= 0 &&
[Year-1] > 0 )

))
 
Thank you very much!

View solution in original post

10 REPLIES 10
tex628
Community Champion
Community Champion

Try this measure, if it works for lost customers we can easily make one for each segmentation:

Lost Customers =
VAR Year = MAX(Calendar[Year])
VAR Year-1 = Year -1 
VAR Year-2 = Year -2 
Return
CALCULATE(
COUNTROWS(
SUMMARIZE( 
Customers[ID],
"Year" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year) ,
"Year-1" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-1) ,
"Year-2" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-2)
)),
[Year] = BLANK(),
[Year-1] = BLANK(),
[Year-2] <> BLANK()
)


Br,
J


Connect on LinkedIn

Hi Johannes,

I've applied your formula and noted this errors highlightened (please look at the italian tables and fields names which I've translated before for a better understanding in the community... fatturato = SELLINGS, codice_cliente_statistico = customer id)

 

gabrielefugazzi_0-1603879097755.png

 

Since an error is detected on the filters applied to the summarize function I've tried the following:

 

Lost Customers =
VAR Year = MAX(fatturato[anno_fattura])

Return
CALCULATE(
COUNTROWS( FILTER (
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,
"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1)),

[Year] = BLANK() &&
[Year-1] <> BLANK() )

))

 

 

But the result returned is still BLANK 🙄.

 

Thank for your help

There is a ")" too much here:

image.png


Connect on LinkedIn

Removing it produces the following error (always on the filter clause)

 

gabrielefugazzi_1-1603880145999.png

 

My badd, wasn't thinking straight! You should be totally correct with the filter() approach which concerns me as to why you're getting a blank result there. If you just do a normal countrows without any filters, what result are you getting? 

Lost Customers =
VAR Year = MAX(Calendar[Year])
Return
COUNTROWS(
SUMMARIZE( 
Customers[ID],
"Year" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year) ,
"Year-1" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-1) ,
"Year-2" , CALCULATE( SUM(Sellings[Price]) , ALL(Calendar) , Calendar[Year] = Year-2)
)




Connect on LinkedIn

Not problem at all! I'm just learning

 

Doing as suggested I've defined the formula as follows:

 

Lost Customers =
VAR Year = MAX(fatturato[anno_fattura])

Return
CALCULATE(
COUNTROWS(
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,
"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1))

))
 
From this formula the output should be the total number of customers with their total sale amount per year (if I'm correct). The output is 10826.
 
Kind of strange result because running the query in excel (connected to the tabular model) the result is 5791 customers in 2019 and 2020 (same period selected in PowerBI).
 
I've also made an attempt to change COUNTROWS with DISTINCTCOUNT but wasn't successfull because that combination of function is refused by DAX (Parameter is not of the correct type).
 
I'm wrong at something but cannot see it.

With 2020 selected in the slicer. This should return the exact number of distinct customers with sales in either 2020 or 2019. 

If you add this in a table with your customer column you should get the sales for 2020. Might make it easier to compare and see if things are being calculated correctly:

2020 = 
SUMX(
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year) ,
"Year-1" , CALCULATE( SUM(fatturato[fatturato]) , ALL(fatturato) , fatturato[anno_fattura] = Year-1)), 
[Year]
)


Br,
J

 





Connect on LinkedIn

Playing with the formula you provided and changing it I've came to the desidered solution:

 

Lost Customers =
VAR Year = MAX(fatturato[anno_fattura])
Return
CALCULATE(
COUNTROWS( FILTER (
SUMMARIZE(
fatturato, fatturato[codice_cliente_statistico],
"Year" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year)) ,
"Year-1" , IF(ISBLANK(CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1)), 0, CALCULATE( SUM(fatturato[fatturato]) , fatturato[anno_fattura] = Year-1))) ,

[Year] <= 0 &&
[Year-1] > 0 )

))
 
Thank you very much!

Wonderful to hear! Good look with future calculations 🙂 

Br,
J


Connect on LinkedIn

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors