Combining results from many tables into one visual

[Anonymous]

Hi,

I am trying to create a quarterly scorecard with results from 4-5 different tables and then matching them with their quartley goals. I have one to many relationships with the Goals to each of the data tables but I am trying to think of the best way of aggregating this.

I can't seem to use related but am thinking there must be a concise way of taking the data tables and matching it up to the appropriate quarterly goal.

Does anyone have any advice?

Thanks

Goals Table
Metric	Quarter	Metric Quarter	Goal
Projects Completed	2023 Q2	Projects Completed-2023 Q2	200
Defects	2023 Q2	Defects-2023 Q2	100
Employees	2023 Q2	Employees-2023 Q2	50
Exceptions	2023 Q2	Exceptions-2023 Q2	20
Projects Completed	2023 Q3	Projects Completed-2023 Q3	250
Defects	2023 Q3	Defects-2023 Q3	40
Employees	2023 Q3	Employees-2023 Q3	150
Exceptions	2023 Q3	Exceptions-2023 Q3	15
		-
Data Tales
Projects Completed
Defects
Employees
Excepions
Desired Output
	2023 Q2	2023 Q2	2023 Q3	2023 Q3
	Goal	Actual		Actual
Projects Completed	200		250
Defects	100		40
Employees	50		150
Exceptions	20		15

[Anonymous]

Does anyone else have a simpler solution?

[Anonymous]

Would you mind sharing the PBIX? It seems great but there is something I am missing.

Thanks

[Anonymous]

The reason being is that I need to not select a particular quarter but really need the scorecard for every quarter for all 4 metrics.  Thanks!

[Sahir_Maharaj]

Hello @Anonymous,

Can you please try the following approach:

1. Create measures (Repeat for Defects, Employees, and Exceptions)

Total Projects Completed = SUM('Projects Completed'[Value])

2. Retrieve goals from the Goals Table

Goal Value = 
LOOKUPVALUE(
    'Goals Table'[Goal],
    'Goals Table'[Metric], SELECTEDVALUE('Metrics Table'[Metric]),
    'Goals Table'[Quarter], SELECTEDVALUE('Metrics Table'[Quarter])
)

3. Combine actuals and goals

Actuals by Quarter = 
SWITCH(
    TRUE(),
    'Metrics Table'[Metric] = "Projects Completed", [Total Projects Completed],
    'Metrics Table'[Metric] = "Defects", [Total Defects],
    'Metrics Table'[Metric] = "Employees", [Total Employees],
    'Metrics Table'[Metric] = "Exceptions", [Total Exceptions]
)

Hope this helps.