Matrix - Non Summed Value?

[MorganKlaif]

Hello,

I'm working on a report and I'm having difficulty trying to get the correct amount to be used. The report is using a matrix view. I'm trying to show the capacity of some activities and the % of that capacity based on the number of people enrolled. Here's a quick preview of the matrix:

I have two tables called TRANSACTIONS and ACTIVITIES. These are merged together based on the column of "Activity ID". I've expanded ACTIVITIES in the merged query to include "Activity Category" (used as a filter), "Site Name" (used in the matrix), "Season" (used as a filter), and "Enrollmax" (used in the matrix).

In the preview image above, the East Orange Capacity for January 2024, is currently showing 1428, when I'm trying to get it to show 92. It's causing the % Capacity to give 4% (61/1428), when it should be 66% (61/92).

My % Capacity measure is:

% Capacity = DIVIDE(DISTINCTCOUNT(dim_DATA[CUSTOMER_ID]),SUM(dim_DATA[stg_ACTIVITIES.ENROLLMAX]),0)

The matrix view is setup like the following:

• Row = "Site Name" from the expanded ACTIVITIES in the merged table.
• Column = "Creation Date" from TRANSACTIONS in the merged table. I'm using the hierachy to show year and month.
• Values = DISTINCT COUNT of "Customer ID" from TRANSACTIONS for Enrollment, a measure for Enrollment Goal, a measure for % Capacity, "Enrollmax" from the expanded ACTIVITES in the merged table for Capacity, and a measure for Revenue Goal.

The "Enrollmax" from the expanded ACTIVITIES seems to be multiplying the amount of Customer ID by the Enrollmax.

Taking two of the sports activities from East Orange, for example, one has an Enrollmax of 12 and 4 people in it, and the other has an Enrollmax of 20 and 11 people in it. It's currently summing Enrollmax as 48 and 220 for 268, instead of 12 and 20 for 32.

When I try to "not summarize" Enrollmax, it doesn't do anything.

Is there a way to not have it multiply by the Customer ID, so it only sums the actual Enrollmax? It's a lot of data and hard to come up with example data, so I can't really share anything.

ADDED EDIT:

I've uploaded a sample report with sample data to: https://drive.google.com/file/d/1sZ5oLzPOqh-Jk9jxn6vUJvsjvewob3Yj/view?usp=sharing

What I'm expecting to see would be:

Year	2024
Branch	January	February
East Orange YMCA
Enrolled	6	15
% Capacity	19%	47%
Capacity	32	32

What I currently have is:

Year	2024
Branch	January	February
East Orange YMCA
Enrolled	6	15
% Capacity	7%	6%
Capacity	88	268

Capacity should only be the sum of the two activites' "ENROLLMAX", but it appears to be multiplying by the number of people enrolled.

Then % Capacity should be Enrolled divided by Capacity.

Thank you!

[MorganKlaif · Accepted solution]

This has been resolved.

After looking up how the merge functions, and knowing that I used a LEFT OUTER join, I found out that I needed to enable load on my ACTIVITIES table, create a relationship with the MERGED table based on ACTIVITY_ID, which is what I used for the join, and I was able to use the ENROLLMAX from ACTIVITIES directly to correctly SUM, rather than multiply.

[Greg_Deckler]

@MorganKlaif Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[MorganKlaif]

Hi @Greg_Deckler Greg,

Thank you for getting back to me, and sorry for the delayed response.

I've uploaded a sample report with sample data to: https://drive.google.com/file/d/1sZ5oLzPOqh-Jk9jxn6vUJvsjvewob3Yj/view?usp=sharing

What I'm expecting to see would be:

Year	2024
Branch	January	February
East Orange YMCA
Enrolled	6	15
% Capacity	19%	47%
Capacity	32	32

What I currently have is:

Year	2024
Branch	January	February
East Orange YMCA
Enrolled	6	15
% Capacity	7%	6%
Capacity	88	268

Capacity should only be the sum of the two activites' "ENROLLMAX", but it appears to be multiplying by the number of people enrolled.

Then % Capacity should be Enrolled divided by Capacity.