@CStaich,
For what initially looked like a fairly simple issue, this turned out to be deceptively difficult. What was challenging was constructing the data model that allowed for one, two or three different salespeople, and the sales amount being attributed individually to each one.
Here’s your original data model:
This had one minor, one major and one fatal problem:
- date table not marked as a date table (easily fixable, but without that the time intelligence functions will not work properly)
- bidirectional relationship between the date table and the fact table (bidirectional relationships should be avoided if at all possible, since they can produce unexpected, incorrect results)
- multiple relationships between the salesperson dimension table and the fact table - given your requirements, this structure will never produce the correct results since you always have to choose one of the three relationships as active (or activated via USERELATIONSHIP), and by doing so you will fail to capture the salespeople in the other two fields.
To remedy these problems, I did the following:
- marked the date table as a date table, validating on the [Dates]Date field
- changed the date table to fact table relationship to a one-way, one-to-many
- in Power Query, unpivoted the Inside, Field, and Other Salespeople fields, resulting in the following structure that allowed for a single, active relationship between the salesperson dimension table and this restructured fact table:
This now results in a much simpler, cleaner standard star schema data model:
However, this is not without its own challenges, in that the sales amount for each order is repeated up to three times, depending on how many salespeople were involved in the sale. Thus if you take a simple SUM() of this column, it will contain multiple duplicate values. However, we can deal with that if needed via DAX, but the model as structured addresses your requirement well, I think.
Now, onto the calculated columns and measures.
The first one is a calculated column in the salesperson dimension table to determine whether someone made a sale in any of the three salesperson categories. You’ll see that the new structure simplifies this measure significantly relative to what we discussed above earlier in this thread:
Made Any Sales =
IF(
CONTAINS(
'Sales Orders by Salespeople',
'Sales Orders by Salespeople'[Salesperson Code],
Salespeople[Salesperson Code]
),
1,
0
)
Now here’s the time intelligence measure that calculates YTD total amounts by salesperson, attributing the sale to every salesperson involved, and again the revised data model simplifies this significantly:
YTD Sales by Salesperson =
CALCULATE(
TOTALYTD(
[Total Sales],
Dates[Date],
ALLSELECTED( Dates )
),
Salespeople[Made Any Sales] = 1
)
Here’s what it looks like when it’s all put together: (note that I changed your data to eliminate any sales for Xavier Zulu, in order to better test the Made Any Sales calculated column):
I hope this is helpful. Full solution file posted below.
Please give a shout if you have any other questions about this.
P.S. if you haven’t already gone through the following course, I highly recommend doing so, as it is a terrific primer on the data modeling and transformation issues discussed above:
