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.
