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

Helper I

HOW MANY CUSTOMERS WE'VE LOST THIS YEAR?

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

 LABEL IDENTIFIES -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
Helper I

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!
10 REPLIES 10
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

Helper I

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)

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 🙄.

Community Champion

There is a ")" too much here:

Helper I

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

Community Champion

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

Helper I

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.
Community Champion

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

Helper I

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!
Community Champion

Wonderful to hear! Good look with future calculations 🙂

Br,
J

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 - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors