Help: DistinctCount by DistinctCount

[SS-PBI]

Good Morning,

I am trying to get the distinctcount of a column by the distinct count of another. 

I have a table with 3 columns, Date, Volunteer, and Location.

I need to calculate the percent of volunteers working by the count of distinct locations they volunteered at, as shown in the image below, the table visual on the right. I use a slider to select the date ranges. So far, I am having difficulty in Power BI to get this working correctly.

The table on the right is based on a SUMMARIZE function to create a new table that has Volunteer and Count of Location (distinct count of locations volunteered at), and then I can create a measure with the total number of distinct volunteers and another measure with the distinct count of volunteers. A third measure is used to calculate the percentage.

My problem is the SUMMARIZE function doesn't take the date slider into account. How do I go about doing this best in Power BI?

My file is here:

https://algonquinlivecom-my.sharepoint.com/:u:/g/personal/robinss_algonquincollege_com/EcesszcFjeVHk... 

UPDATE: Changed link to OneDrive location

@v-yiruan-msft 

[v-yiruan-msft · Accepted solution]

Hi @SS-PBI ,

Sorry for delay... You can follow the below steps to achieve it, please find the details in Page 2 of attachment:

1. Create a calculated table Location number

Location number = GENERATESERIES(1,DISTINCTCOUNT('Volunteer Data'[Location]),1)

2. Create a measure to get the number of volunteer per location number group

Count of Volunteers = 
VAR _tab =
    SUMMARIZE (
        'Volunteer Data',
        'Volunteer Data'[Volunteer],
        "countofL", CALCULATE ( COUNT ( 'Volunteer Data'[Location] ) )
    )
VAR _tab2 =
    FILTER (
        CROSSJOIN ( 'Location number', _tab ),
        [countofL] = 'Location number'[Number]
    )
RETURN
    COUNTAX ( _tab2, [Volunteer] )

3. Update the formula of measure [Percent Volunteers]: replace measure [Count of Volunteers 2] with new created measure [Count of Volunteers]

Percent Volunteers = [Count of Volunteers] / [Total Unique Volunteers]

Best Regards

[v-yiruan-msft]

Hi @SS-PBI  ,

I'm sorry that I have no access to your file. Could you please upload your file to OneDrive for Business, then share the file link with me. Please find the details in the following documentation.

Share OneDrive files and folders

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.