Looking to Automate Distinct Count & Prevalence Calculations

Hello sunflowermama013 and Welcome to Excel Forum.
Perhaps the following will help.
1. In the data model add a Concatenate column using: =CONCATENATE(Table1[Restaurant],Table1[ID])
2. Add a Rows column using: =COUNT(Table1[ID])
3. Add a Distinct Count measure using: =DISTINCTCOUNT(Table1[Concantenate])
4. Add a Prevalence measure using: =[Distinct Count]/MAX(Table1[Rows])
5. Pull the Distinct Count and Prevalence fields into the Values area of the pivot table
Let us know if you have any questions.

Power Query

Please Login or Register  to view this content.

after that create Pivot Table

Count of Restaurant	ID
Restaurant	1	2	3	4	5	Grand Total
Brusters	1	1	1			3
Burger King	1	1			1	3
Chick-fil-A	1	1		1		3
Freddy's			1		1	2
McDonalds	1					1
Taco Bell		1			1	2
Grand Total	4	4	2	1	3	14

and then

21%		=K$4:K$9/GETPIVOTDATA("Restaurant ",$E$2)
21%
21%
14%
7%
14%

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.