Attainment to Goal by Year

Power BI Data Model
1

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:
https://imgur.com/a/Ajj215K

Image below shows field list:
https://imgur.com/a/kQVUhmh

Image below shows data model:
https://imgur.com/a/YbtRphI

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.

2

@CStaich,

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
3

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.

4

@CStaich,

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
5

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 = 
    CALCULATE(
        SUM('Dim Salesperson_Goals'[Quota]), 
        TREATAS(
            VALUES('Dim Salesperson_Purchaser'[Code]), 
            'Dim Salesperson_Goals'[Initials]), 
        TREATAS(
            VALUES('Dim Dates'[Year]), 
            'Dim Salesperson_Goals'[Year])
    )

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

1 Like
6

@CStaich,

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
7

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