Creating a weighted average across 3 factors
I have a problem that I can't seem to resolve easily in PBI. I can get it to work in excel with a series of steps but need a solution for a set of customers, that is filterable from my dimension tables. I need to create a weighted average for a client out of a group of clients, 8 in total. Based on a combination of 3 factors; Driver Age, Vehicle Age, Region. In excel I would work out the distribution of count of claims across a concatenation of these factors as a percentage, example below. Then average the each distinct group. Key: UK Region, Driver Age, Vehicle Age 1 2 3 4 5 6 7 8 Member Average Scotland, < 25, 0 1% 3% 1% 8% 4% 10% 4% 5% 5% Scotland, < 25, 1 2% 6% 2% 5% 5% 2% 7% 5% 4% Scotland, < 25, 2 2% 1% 5% 4% 3% 5% 24% 8% 7% Scotland, < 25, 3 2% 3% 2% 5% 5% 2% 4% 2% 3% Scotland, < 25, 4 2% 6% 2% 4% 1% 5% 9% 2% 4% Scotland, < 25, 5 2% 11% 0% 9% 2% 7% 3% 2% 4% Scotland, < 25, 6 3% 8% 1% 2% 7% 11% 7% 4% 5% Scotland, < 25, 7 1% 10% 8% 4% 2% 6% 1% 1% 4% Scotland, < 25, 8 2% 6% 14% 1% 3% 7% 4% 3% 5% Scotland, < 25, 9 0% 7% 10% 6% 1% 6% 1% 2% 4% Scotland, < 25, 10 0% 16% 11% 6% 1% 5% 4% 2% 6% Scotland, < 25, 11 11% 4% 4% 8% 12% 5% 5% 2% 6% Scotland, < 25, 12 23% 7% 6% 4% 20% 10% 8% 18% 12% Scotland, < 25, 13 17% 2% 22% 13% 14% 7% 4% 16% 12% Scotland, < 25, 14 19% 3% 4% 6% 15% 5% 11% 13% 10% Scotland, < 25, 15 13% 7% 8% 17% 5% 6% 5% 15% 10% Check 100% 100% 100% 100% 100% 100% 100% 100% 100% Once I have that I would apply the new average distribution for all clients to the average cost for each group of factors before adding it back up again to give me a new weight adjusted average cost. The question I'm trying to answer is; what would client X look like if they had the same distribution as the entire group of clients? There could be a better way to do this in PBI but still learning the ropes with only forums for help. Thanks in advance.
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. 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%