Attainment to Goal by Year

I would like to create a measure that displays “Salesperson Goal Current Year”.

I am having trouble setting up my data model. I cannot connect ‘Dim Salesperson_Goals’ to both ‘Dim Salesperson’ and ‘Fact Sales_Invoices_By_Rep’ without creating a circular relationship.

I want to be able to store many years’ goals for each sales rep in the ‘Dim Salesperson_Goals’ table, but am at a loss for how to set up the data model and DAX formula.

I have the following tables;

  • Dim Salesperson

    • Field 1: Salesperson Code
    • Field 2: Salesperson Name
  • Dim Dates

    • Field 1: Date
  • Fact Sales_Invoices_By_Rep

    • Field 1: Sales Invoice No.
    • Field 2: Invoice Date [related to Dim Dates]
    • Field 3: Salesperson Code [related to Dim Salesperson]
    • Field 4: Invoice Total
  • Dim Salesperson_Goals

    • Field 1: Salesperson Code [related to Dim Salesperson]
    • Field 2: Goal
    • Field 3: Some Date Field, perhaps last date of the applicable year [needs a relation]

For some additional context, here’s a snapshot of my current visualization. Everything works as I’d like it to except: no matter which year is sliced, the goal amount is always the same. This is because my current Salesperson Goal measure is simply SUM('Dim Salesperson_Goals'[Goal]) and has no linkage to the dates table.

Image below shows visualization:

Image below shows field list:

Image below shows data model:

Here are my measures:
The goal itself, which is the DAX/Data Modelling I am having trouble filtering appropriately:
Salesperson Goal = SUM('Dim Salesperson_Goals'[Quota])

Attainment to goal (green):
Salesperson Attainment to Goal = IF([Salesperson Goal] - [Invoiced GP by Rep YTD] >= 0, [Invoiced GP by Rep YTD], [Salesperson Goal])
Salesperson Open Booked to Goal = IF([Invoiced GP by Rep YTD] + [Open Booked GP by Rep YTD] <= [Salesperson Goal], [Open Booked GP by Rep YTD], IF([Salesperson Goal] - [Invoiced GP by Rep YTD] >= 0, [Salesperson Goal] - [Invoiced GP by Rep YTD], 0))

Attainment beyond goal (gold):
Salesperson Open Booked Beyond Goal = IF([Invoiced GP by Rep YTD] + [Open Booked GP by Rep YTD] <= [Salesperson Goal], 0, [Open Booked GP by Rep YTD] - [Salesperson Open Booked to Goal])
Salesperson Attainment Beyond Goal = IF([Salesperson Attainment to Goal] < [Salesperson Goal], 0, [Invoiced GP by Rep YTD] - [Salesperson Goal])

How might I go about storing these goal values in a way that allows date filtering without creating a circular relationship?

Note: Currently, my 'Dim Salesperson_Goals' table only stores 2019 goals, otherwise the graphic would be much further removed from reality. I’m trying to get the 2019 goals to disappear when filtering by 2020.


Have you considered merging your Salesperson and Salesperson Goals dimension tables? There will be a slight bit of denormalization, but it will simplify both your data model and your DAX, and eliminate the circular reference problem.

  • Brian

Merging queries… This would be like a LEFT OUTER JOIN in SQL, correct? I would have multiple records for each Salesperson, and create a Many : Many relationship between the 'Dim Salesperson' table and each of my Fact Tables. Is this really the best option? I’ve been advised to avoid Many : Many relationships where ever possible.

EDIT: Is this a case where a Bridge Table would be valuable? I have never used one, but have heard that it may help resolve Many : Many relationship ambiguities.


You’re absolutely right - my bad. Let me take another swing at this and provide you some better advice this time. You could use a bridge table to address this problem, but I think a better approach would be to treat your sales goal table as a disconnected supporting table, and create the virtual relationship to your Sales table via the TREATAS function:

This is very analogous to the way that @sam.mckay advocates treating budget targets in the budgeting and finance coursework:

Personally, I found TREATAS one of the tougher functions to get my head around and used to use bridge tables instead. However, the above video does a great job explaining it, and once it clicks for you I think you’ll find it to be one of the most useful DAX functions.

  • Brian
1 Like

TREATAS worked like a charm! Instead of trying to build this table into my data model, I can simply use this TREATAS function to virtually link the tables. In this case, I don’t think a bridge table would be as clean, and I would not want the added complexity in my data model.

The video was almost identical to the case I’m working with.

Here’s my final measure for Salesperson Goal.

Salesperson Goal = 
        SUM('Dim Salesperson_Goals'[Quota]), 
            VALUES('Dim Salesperson_Purchaser'[Code]), 
            'Dim Salesperson_Goals'[Initials]), 
            VALUES('Dim Dates'[Year]), 
            'Dim Salesperson_Goals'[Year])

Thank you very much for pointing me in the right direction. Cheers!

1 Like


Nice work! Looks like it took you all of 10 minutes to master TREATAS. :grinning:

Thanks for following up and posting your final measure.

  • Brian

The video was very clear, and there were direct parallels between the demo data and mine. You made it easy for me!