Forum Discussion
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.
- Anonymous2 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
- AnonymousNot 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
- MorganKlaif
Helper I
Hi Anonymous,
Apologies for that! I've gone ahead and fixed the sharing permissions. The new link is: https://drive.google.com/file/d/1a4aVarrSKSd8MegjqVa8Pr8_cToJFu9R/view?usp=sharing
- AnonymousNot 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.