Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I Currently have a table that displays the customers and the month that the we gained that customer on or lost the customer, I'm trying to create a new calculated table to show how many customers we gained or lost by month.
The table of customers that I currently have.
Customer Number | Month Of Customer Gain | Month Of Customer Lose |
123 | October | - |
124 | October | - |
125 | September | - |
126 | - | August |
129 | - | October |
The calculated table that I'm trying to create
Month | Gained Customers | Lost Customers |
October | 2 | 1 |
September | 1 | 0 |
August | 0 | 1 |
Solved! Go to Solution.
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description,I think you may need to create a new table with a single column as silcer,then create a measure to filter data in filter pane.
Like this:
Measure =
var tab =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])||
[Month Of Lose] in DISTINCT('Calendar'[Month Name])
)
)
var tab1 =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])
)
)
var tab2 =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Lose] in DISTINCT('Calendar'[Month Name])
)
)
return
IF(
HASONEVALUE(Slicer[Slicer]),
IF(
SELECTEDVALUE(Slicer[Slicer])="join" ,
IF(
MAX('Customer Status'[Customer Number]) in tab1,
1,0
),
IF(
SELECTEDVALUE(Slicer[Slicer])="lose",
IF(
MAX('Customer Status'[Customer Number]) in tab2,
1,0
)
)
),
IF(
MAX('Customer Status'[Customer Number]) in tab,
1,0
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description,I think you may need to create a new table with a single column as silcer,then create a measure to filter data in filter pane.
Like this:
Measure =
var tab =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])||
[Month Of Lose] in DISTINCT('Calendar'[Month Name])
)
)
var tab1 =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Join/rejoin] in DISTINCT('Calendar'[Month Name])
)
)
var tab2 =
CALCULATETABLE(
DISTINCT('Customer Status'[Customer Number]),
FILTER(
ALL('Customer Status'),
[Month Of Lose] in DISTINCT('Calendar'[Month Name])
)
)
return
IF(
HASONEVALUE(Slicer[Slicer]),
IF(
SELECTEDVALUE(Slicer[Slicer])="join" ,
IF(
MAX('Customer Status'[Customer Number]) in tab1,
1,0
),
IF(
SELECTEDVALUE(Slicer[Slicer])="lose",
IF(
MAX('Customer Status'[Customer Number]) in tab2,
1,0
)
)
),
IF(
MAX('Customer Status'[Customer Number]) in tab,
1,0
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Can you share better sample data and sample output in a table format? Or a sample pbix after removing sensitive data. Also what happens if a customer does not come for one month and comes again a month next to it.
This is the sample pbix
I made a partial solution to this. You will find it in the sample, but my issue with my solution is that I can't filter out the specific customers from the customers' list due to it being a measure. I'm trying to find a way to make a calculated table that lets me filter out, for example, a list of the new customers in October.
@Anonymous , it depends on your gain and lost definition, what is that. I have two blogs, refer if those can help
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
if a customer got lost on october it will show october in front of the customer number and under the column "month of lose", basically I would like to transfer the first table to the second table, I've managed to do something with a measure using a virtual relationship with month name from the calendar table but my issue with that measure that I can't filter the customers out if I click on the measure, it only filter the month.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |