Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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!
Solved! Go to Solution.
Playing with the formula you provided and changing it I've came to the desidered solution:
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
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 🙄.
Thank for your help
Removing it produces the following error (always on the filter clause)
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)
)
Not problem at all! I'm just learning
Doing as suggested I've defined the formula as follows:
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
Playing with the formula you provided and changing it I've came to the desidered solution:
Wonderful to hear! Good look with future calculations 🙂
Br,
J
@gabrielefugazzi , refer this blog. And use Datesytd in place of datesmtd
Also refer: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.