Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm struggling to work out how to resolve this query. Hopefully I can explain it well enough!
I have a table that has columns for [Owner], [Placement Id], [Ownership %], [Plc Start] amongst others
I have a measure that counts the number of unique [Placement Id] for a given date range (I'm grouping by week in my date table) which works correctly:
Contract Fills =
VAR MinDate = MIN('Calendar'[WeekStartDate])
VAR MaxDate = MAX('Calendar'[WeekEndDate])
RETURN
CALCULATE (
DISTINCTCOUNT ( FullResults[Placement Id] ),
FILTER ( FullResults, FullResults[Row Type] = "Realised Contract Temp" ),
FILTER ( FullResults, FullResults[Plc Start] >= MinDate && FullResults[Plc Start] <= MaxDate )
)
However, I need to extend this by the Ownership %. I can guarantee that Ownership % is unique to Placement Id but I can't figure out how to multiply the count of Placement Id by the Owernship % for that Placement Id.
Owner | Placement Id | Ownership % |
John | 1 | 100 |
John | 1 | 100 |
Mark | 2 | 100 |
Pete | 3 | 50 |
Mark | 3 | 50 |
Assuming this table is within all in my date filter, I should get:
Owner | Contract Fills | Calculation |
John | 1 | 1 * 100% |
Mark | 1.5 | 1 * 100% + 1 * 50% |
Pete | 0.5 | 1 * 50% |
Hi @ChrisJC ,
I am a bit confused by the top 2 rows of your sample data. Why are these 2 rows identical?
Cheers,
Sturla
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |