Forum Discussion

MorganKlaif's avatar
MorganKlaif
Icon for Helper I rankHelper I
2 years ago
Solved

Getting Percentage of Member Count Divided by Capacity Into Matrix Table

Hello.

 

I'm currently working on a report that can pull a distinct count of members registered for certain childcare programs, based on a date range of the month and year, which includes the percentage of the capacity for that branch.

 

The expected outcome should look like the following:

 

My test report can be found at: https://drive.google.com/file/d/1a4aVarrSKSd8MegjqVa8Pr8_cToJFu9R/view?usp=sharing

 

On the test report, Program Registrations and Programs are joined together by Program ID. A matrix view is using Branch from Programs for the rows, Year and Month on Begin Date from Program Registrations for the columns, and Count Distinct on Customer IDs from Program Registrations for the values.

 

This matrix view is pulling the numbers in correctly, however, I've been unable to find a way to get the percentage added, so I tried doing a second matrix view, but I don't think I'm doing it correctly.

 

The capacity is a conditional column on Programs based on the Branch name (60 for East Orange and 88 for Greater Bergen). In the above image, Sept 2021 East Orange is 52% for 31/60 and Sept 2021 Greater Bergen is 49% for 43/88.

 

For the second matrix, where I'm trying to get the percentage added, my current column is set as:

 

% Capacity = DISTINCTCOUNT('Program Registrations'[Customer ID]) / Programs[2021-2022 Capacity]

 

 

It seems to only want to SUM or COUNT.

 

For the test report, Sept 2021 East Orange should be showing 2 and 3.33% (2/60) and Sept 2021 Greater Bergen should be showing 1 and 1.13% (1/88).

 

Is there a way to get the percentage to show on the first matrix without needing a second, and how would I redo the column to show it?

 

Thank you.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MorganKlaif ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) Change the data type of the [2021-2022 Capacity] column.

     

    (2) We can create a measure. 

    Measure = DIVIDE(DISTINCTCOUNT('Program Registrations'[Customer ID]),SUM('Programs'[2021-2022 Capacity]),0)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi MorganKlaif ,

     

    I don't have access to your pbix, please re-share the link after modifying the sharing permissions.

     

    Best Regards,

    Neeko Tang

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi MorganKlaif ,

         

        According to your description, here are my steps you can follow as a solution.

        (1) Change the data type of the [2021-2022 Capacity] column.

         

        (2) We can create a measure. 

        Measure = DIVIDE(DISTINCTCOUNT('Program Registrations'[Customer ID]),SUM('Programs'[2021-2022 Capacity]),0)

        (3) Then the result is as follows.

        Best Regards,

        Neeko Tang

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