Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |