Dynamic Allocation from different table

[EAF003]

I am trying to create a table or table visual that accomplishes the following, but cannot figure out either the measures/calculations needed to create it. There is an 'opportunity' by Rep table and a monthly 'target' by Division table (see below):

'target'

Division	Div_Mo_Target
SMB	12
MM	6
ENT	4

'opportunity'

Division	Rep	Opportunity	Closed Date
ENT	Amy	DKN	1/31/2025
ENT	Amy	SAD	1/31/2025
ENT	Amy	VNF	2/28/2025
ENT	Jake	TXC	1/31/2025
ENT	Jake	VIE	1/31/2025
ENT	Jake	SDE	2/28/2025
ENT	Jake	WOE	2/28/2025
ENT	Tina	FLW	1/31/2025
ENT	Tina	ELT	2/28/2025
MM	Sarah	JMW	1/31/2025
MM	Sarah	WSH	2/28/2025
MM	Sarah	JJS	2/28/2025
MM	Tom	OLE	1/31/2025
MM	Tom	ONV	1/31/2025
MM	Tom	AAB	2/28/2025
MM	Tom	XYZ	2/28/2025
SMB	Bob	KSI	1/31/2025
SMB	Bob	WER	1/31/2025
SMB	Bob	QAS	1/31/2025
SMB	Bob	JMI	2/28/2025
SMB	Bob	KOW	2/28/2025
SMB	Bob	TTO	2/28/2025
SMB	Joe	XHD	1/31/2025
SMB	Joe	EIT	1/31/2025
SMB	Joe	FRL	1/31/2025
SMB	Joe	MKE	1/31/2025
SMB	Joe	COR	2/28/2025
SMB	Joe	PBR	2/28/2025
SMB	Joe	EOP	2/28/2025
SMB	Joe	JVV	2/28/2025
SMB	Mark	KVV	1/31/2025
SMB	Mark	HHW	1/31/2025
SMB	Sally	JKY	1/31/2025
SMB	Sally	QUV	1/31/2025
SMB	Sally	POS	2/28/2025
SMB	Sally	MMMB	2/28/2025

I am trying to produce a table or matrix visual that looks like the following but am not certain of the best method to accomplish. Essentially, I want to allocate the monthly target by division by the active reps and then show all reps (active or not) actuals by month. As you can see below the visual is filtered based on which Divisions are selected by the user. 

Any guidance would be greatly appreciated!!!!

Division	Rep	Mo Target	Jan-25	Feb-25
MM	Sarah	3	1	2
MM	Tom	3	2	2
MM SUBTOTAL		6	3	4
SMB	Bob	4	3	3
SMB	Joe	4	4	4
SMB	Mark		2	0
SMB	Sally	4	2	2
SMB SUBTOTAL		12	11	9
TOTAL		18	14	13

[EAF003]

Perfect! Thank you so much

[v-pbandela-msft]

Hi @EAF003,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @EAF003,

Thank you for reaching out in Microsoft Community Forum.

Thank you @bhanu_gautam    for the helpful response.

As suggested by bhanu_gautam,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[EAF003]

@bhanu_gautam 
Thank you so much for your quick response!

A couple questions:
1) For the ActiveRep variable how can I adjust to only COUNT (distinct) reps as of the MAX closed date? 
2) On the Monthly Target measure it is using the Target of the Division with the MAX opportunities, can this be adjusted to reflect the Division(s) that are chosen from the Slicer?

[bhanu_gautam]

@EAF003 Adjust ActiveRep to Count Distinct Reps as of the MAX Closed Date:

MoTarget =
VAR MaxClosedDate = CALCULATE(MAX('opportunity'[Closed Date]))
VAR ActiveReps = CALCULATE(
DISTINCTCOUNT('opportunity'[Rep]),
'opportunity'[Closed Date] = MaxClosedDate
)
RETURN DIVIDE(
MAX('target'[Div_Mo_Target]),
ActiveReps,
0
)

To ensure the monthly target measure reflects the selected division(s) from the slicer, you can use the SELECTEDVALUE function to dynamically get the division from the slicer context. Here is the adjusted DAX code:

MoTarget =
VAR SelectedDivision = SELECTEDVALUE('opportunity'[Division])
VAR MaxClosedDate = CALCULATE(MAX('opportunity'[Closed Date]))
VAR ActiveReps = CALCULATE(
DISTINCTCOUNT('opportunity'[Rep]),
'opportunity'[Closed Date] = MaxClosedDate,
'opportunity'[Division] = SelectedDivision
)
RETURN DIVIDE(
CALCULATE(MAX('target'[Div_Mo_Target]), 'target'[Division] = SelectedDivision),
ActiveReps,
0
)

[bhanu_gautam · Accepted solution]

@EAF003 

Make sure there is a relationship between the 'Division' columns in both tables.

Calculate the monthly target for each rep by dividing the division's monthly target by the number of active reps in that division for each month.

DAX
Mo Target =
VAR ActiveReps =
CALCULATE(
DISTINCTCOUNT('opportunity'[Rep]),
ALLEXCEPT('opportunity', 'opportunity'[Division], 'opportunity'[Closed Date])
)
RETURN
DIVIDE(
MAX('target'[Div_Mo_Target]),
ActiveReps,
0
)

Calculate the number of opportunities closed by each rep for each month.

DAX
Jan-25 =
CALCULATE(
COUNT('opportunity'[Opportunity]),
MONTH('opportunity'[Closed Date]) = 1,
YEAR('opportunity'[Closed Date]) = 2025
)

Feb-25 =
CALCULATE(
COUNT('opportunity'[Opportunity]),
MONTH('opportunity'[Closed Date]) = 2,
YEAR('opportunity'[Closed Date]) = 2025
)

Add a Matrix visual to your report.
Drag 'Division' and 'Rep' to the Rows.
Drag the measures 'Mo Target', 'Jan-25', and 'Feb-25' to the Values.
Ensure that the matrix is set to show subtotals and grand totals.