Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic Allocation from different table

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...
Need Help
Tutorial Requests
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous 

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous 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
    )