Matrix - Adding Another Value Is Forcing SUM When I Need It Based On Date

[MorganKlaif]

Good afternoon,

I have a matrix view that looks like the following:

"Branch" is set as the row. Branch comes from a table called Programs.

"Date Year and Month" is set as the column. Date comes from a table called Program_Registrations.

Programs and Program_Registrations are tied together on a relationship of Program_ID.

For the values, "Enrollment" is the distinct count of customer IDs from Program_Registrations and "% of Capacity" is a measure that divides the enrollment amount by a calculated column in Programs that provides the capcity number based on the branch name.

We wanted to add in 2024 goals, so we could see where we're at each month.

I created a new table called "2024 Goals" with three columns of Branch, Date, and Goal. I tied this table to Programs on a relationship of Branch. The table looks like:

Branch	Date	Goal
East Orange YMCA	1/1/2024	43
South Mountain YMCA	1/1/2024	190
West Essex YMCA	1/1/2024	62
Wayne YMCA	1/1/2024	49
East Orange YMCA	2/1/2024	43
South Mountain YMCA	2/1/2024	192
West Essex YMCA	2/1/2024	62
Wayne YMCA	2/1/2024	51

When I try to add in Goals to the matrix view, it forces a SUM even though I have column tools selected as "don't summarize". Here's what it looks like:

For example, at South Mountain, January's goal is 190 and February's goal is 192. Instead of showing 190 and 192 under January and February respectively, it's a SUM of all the goal amounts per branch.

Is there a way to not get Goals to SUM, but instead show the appropriate goal for that month?

[v-kaiyue-msft · Accepted solution]

Hi @MorganKlaif ,

The dates do differ, so they need to correspond to the year and month, try modifying the expression to:

Goal Value = LOOKUPVALUE('2024 Goals'[GOAL], '2024 Goals'[SITE_NAME], MAX(Programs[SITE_NAME]),'2024 Goals'[DATE].[Month],MAX(Program_Regisrations[BEGIN_DATE_ONLY].[Month ]),'2024 Goals'[DATE].[Year], MAX('Program_Regisrations'[BEGIN_DATE_ONLY].[Year]))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[MorganKlaif]

Hi @v-kaiyue-msft Clara,

The new measure you provided worked out perfectly and exactly what's needed! Thank you!

- Morgan

[v-kaiyue-msft]

Hi @MorganKlaif ,

Try creating the following measure:

Goal Value = LOOKUPVALUE('2024 Goals'[Goal], '2024
Goals'[Branch], MAX(Programs[Branch]), '2024 Goals'[Date], MAX(Program_Registrations[Date Year and Month]))

Then replace the "Goals" column in the matrix with the metric you created. Make sure that the relationship between the "2024 Goals" table and the other tables is set up correctly to support the expected behavior. Since you are working with dates, make sure that the relationship can be filtered by both branch and date to allow the metric to retrieve the correct target value.

If this does not solve your issue, please share the following pbix file with sample data to better help you solve your problem.

Best Regards,

Clara Gong

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

[MorganKlaif]

Hi @v-kaiyue-msft Clara,

Thank you for getting back to me!

When trying to use that measure, it brings it up as blank.

Would it be because my relationships are wrong, or would it be because the dates for the 2024 Goals table are based on the 1st of each month (1/1/2024, 2/1/2024, etc) and the dates for the Program_Registrations table are all different based on when the member is going (Monday 1/8/2024, Tuesday 1/9/2024, etc)?

I created a PBIX file for you with example data. For the Program_Registrations, I only used South Mountain for the SITE_NAME/Branch, so there wasn't too much data.

The link to the file can be found at: https://drive.google.com/file/d/1qiy645vjmjLbWLJVfBqxQ-IEzncJOVsw/view?usp=sharing

Thank you for your help so far!

- Morgan