cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Super User

@Anonymous

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/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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous

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/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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.