cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kyledos
Frequent Visitor

Sum of count in a new column/table?

Hello everyone,

 

Currently i am working on a customer churn dashboard. It's almost finished except for one thing which I cant solve. I want to calculate for each customer for how long he or she has been a subscriber in the last 3 years (My dataset contains only 3 years of data). What i did so far is that I entered the count of churners in the value slot and the ID's of customer in the axis slot. With this graph I can see for how many years each customer has been subscribed. however, I want to convert this data to a table. This is what I had in mind:

IDCount of churn by ID

2100005

3
21000061
21000071

 

Im kind of new to PowerBI so i'm not sure how to convert this sum of count per ID in a new column/table.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Kyledos

How many year  or  Count of churn by ID

one seems like

datediff(min[subscribe Date]), Max(Date[Date]),Year)

 

averaged(Values(Table[Customer]),datediff(min[subscribe Date]), Max(Date[Date]),Year) )

 

Where Date[Date] is date from date table used in slicer

 

In case you need bucketing  - https://www.youtube.com/watch?v=CuczXPj0N-k

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 

refer if needed

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@Kyledos

How many year  or  Count of churn by ID

one seems like

datediff(min[subscribe Date]), Max(Date[Date]),Year)

 

averaged(Values(Table[Customer]),datediff(min[subscribe Date]), Max(Date[Date]),Year) )

 

Where Date[Date] is date from date table used in slicer

 

In case you need bucketing  - https://www.youtube.com/watch?v=CuczXPj0N-k

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

 

refer if needed

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.