Forum Discussion
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.
4 Replies
Hi SteveMc ,
There is very little info on how does your calculation work in excel.
Can you share how your table looks like.
Share some sample data and the expected output.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)Thanks Harsh
The average costs I mention are below, to match prev sample.
Key: UK Region, Driver Age, Vehicle Age 1 2 3 4 5 6 7 8 Member Average Scotland, < 25, 0 6,587 5,940 6,551 4,864 6,207 4,864 5,652 6,655 5,915 Scotland, < 25, 1 6,245 4,268 5,808 6,655 4,841 6,655 5,618 6,177 5,783 Scotland, < 25, 2 6,142 5,468 6,344 6,177 5,841 6,177 5,594 5,276 5,877 Scotland, < 25, 3 4,261 5,172 5,168 5,276 4,530 5,276 4,864 6,201 5,094 Scotland, < 25, 4 7,420 5,286 6,607 6,201 5,421 4,864 6,655 4,965 5,927 Scotland, < 25, 5 6,081 7,151 6,413 4,965 6,774 6,655 6,177 8,180 6,550 Scotland, < 25, 6 5,572 4,159 5,945 8,180 5,197 6,177 5,276 6,236 5,843 Scotland, < 25, 7 5,307 4,317 5,989 5,986 4,887 5,276 6,201 5,924 5,486 Scotland, < 25, 8 5,091 4,822 5,892 7,104 4,718 6,201 4,965 5,990 5,598 Scotland, < 25, 9 7,104 5,574 6,236 7,631 8,302 4,965 8,180 5,987 6,747 Scotland, < 25, 10 7,631 5,686 5,924 4,453 3,833 8,180 5,986 6,021 5,964 Scotland, < 25, 11 4,453 4,217 5,990 7,104 4,924 5,986 4,794 6,413 5,485 Scotland, < 25, 12 4,845 4,555 5,987 7,631 5,310 4,587 4,580 5,945 5,430 Scotland, < 25, 13 4,980 5,034 6,021 4,453 5,362 5,047 5,057 5,989 5,243 Scotland, < 25, 14 3,742 3,749 5,443 4,845 4,168 4,507 4,211 4,685 4,419 Scotland, < 25, 15 5,360 5,238 6,104 4,980 6,127 4,910 6,027 6,543 5,661 Then the result I would expect as a Weight Adjusted Cost, from the the formula =SUMPRODUCT($V8:$V23,X8:X23) this woudl take the member average % distribution ($V8:$V23) against the range of average costs for client 1 only.
1 2 3 4 5 6 7 8 Weight Adjusted Cost 5,456 4,935 6,009 5,952 5,346 5,484 5,420 6,000 Hope that makes sense, thanks.
Hi, I still need some help with this as struggling to resolve.
