Calculation of tenure, Member_Month and PMPM

[Luiscv73]

Hello everyone in this great community, I bringing a new challenger that I have to see who can help me.

I have a Members table similar to the following sample:

Member_ID	Member_Name	Month	Membership_type
Mbr1	Member1	1	Gold
Mbr1	Member1	2	Gold
Mbr1	Member1	3	Gold
Mbr1	Member1	4	Gold
Mbr2	Member2	3	Gold
Mbr2	Member2	4	Gold
Mbr2	Member2	5	Gold
Mbr2	Member2	6	Gold
Mbr2	Member2	7	Gold
Mbr2	Member2	8	Gold
Mbr2	Member2	9	Gold
Mbr3	Member3	1	Silver
Mbr3	Member3	2	Silver
Mbr4	Member4	2	Silver
Mbr4	Member4	3	Silver
Mbr4	Member4	4	Silver
Mbr4	Member4	5	Silver
Mbr5	Member5	1	Bronze
Mbr5	Member5	2	Bronze
Mbr5	Member5	3	Bronze
Mbr5	Member5	4	Bronze
Mbr5	Member5	5	Bronze
Mbr5	Member5	6	Bronze

I need calculate Tenure by member as count of how many months each Member_ID has. Example, tenure for Mbr1 is 4, Mbr2 is 7, Mbr3 is 2, as shown below.

Member_ID	Tenure
Mbr1	4
Mbr2	7
Mbr3	2
Mbr4	4
Mbr5	6

Then I need a summary as count of Member_ID by Tenure. Example, Tenure 2 has 1 count, Tenure 4 has 2 counts, Tenure 6 has 1 count and Tenure 7 has 1 count.

Then I need Member Month as the sum of the multiplication of each Tenure by the count of Member_ID in it. Example, Sum(2x1,4x2,6x1,7x1)= 23, I need that number to calculate Per Member Per Month (PMPM) cost later.

I achieved all by a "group by" query in power query that have a colunm of tenure and another one with Member_ID count and measures in DAX, the issue is it doesn't change with a slicer to filter by Membership_type.

I have another version with a DAX summary table, columns Member_Id and Tenure, as:

I created a relationship between this Tenure_Table and Members table by Member_Id, so now I have a visual table that changes the values for each tenure with the slicer by Membership_type. The problem here is that I don't know how to continue, to make de sum of the product Tenure x Count_by_Tenure. The sample below is the visual table that move with the slicer, the columns _Test Tenure are different formulas I used to get the count by tenure, one of them is  Count_by_Tenure = AVERAGEX( KEEPFILTERS(VALUES('Tenure_Table'[Tenure])), CALCULATE(COUNTA('Tenure_Table'[Member_ID]))

I appretiate any idea, thank you in advance.

[v-jianboli-msft]

Hi @Luiscv73 ,

It seems that you can achieve your goal by using the sum of Tenure directly:

If I misunderstand you, Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li