Forum Discussion

ChrisJC's avatar
ChrisJC
Icon for Helper I rankHelper I
6 years ago

DAX 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.

 

OwnerPlacement IdOwnership %
John1100
John1100
Mark2100
Pete350
Mark350

Assuming this table is within all in my date filter, I should get:

OwnerContract FillsCalculation
John11 * 100%
Mark1.51 * 100% + 1 * 50%
Pete0.51 * 50%

1 Reply

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar

    Hi ChrisJC ,

    I am a bit confused by the top 2 rows of your sample data. Why are these 2 rows identical?

     

    Cheers,
    Sturla