Calculate a grouped count based on a previous grouped count

[Anonymous]

I have a table with Customer IDs and Contact IDs. I need to count how many Contacts I have per Customer, which I can get. I need to then count how many Customers are at each count of touchpoints.

Example:

Customer ID	Contact ID
1	100
1	101
1	102
2	103
2	104
3	105
3	106
3	107

Then goes to:

Customer ID	Contact Count
1	3
2	2
3	3

Then:

Customer Count per Contact Count	Contact Count
1	2
2	3

I've tried this but it isn't working. I'm not sure I'm on the right track or not.

Measure Test =

VAR __Table =

GROUPBY(

'Contact History Five Quarters Ago',

'Contact History Five Quarters Ago'[Contact_Donor_Id],

"Count",SUM('Contact History Five Quarters Ago'[Contact_Count])

)

var __table2 =

GROUPBY(

__Table,

[Count],

"Count2",COUNTX( CURRENTGROUP(),'Contact History Five Quarters Ago'[Contact_Donor_Id])

)

RETURN __table2

Thanks,

Sean

[Anonymous · Accepted solution]

Hi @Anonymous ,

Another way you can do it is using Group By feature in Query Eidtor with two group by operations.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Greg_Deckler]

A little tough to follow but sounds like you perhaps want a disconnected table. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...