Forum Discussion
ChrisJC
Helper I
6 years agoDAX Measure - weighted distinct count
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% |