Applying different weights on each level of hierarchy
Hello, I have PnL fact table like this: fact_table([id],[date],[account_key],[sales_amount]) and dimension for PnL hierarchy like this: dimension_table([account_key],[level_1_name],[level_2_name],[level_2_weight],[level_3_name],[level_3_weight],[level_4_name],[level_4_weight],[level_5_name],[level_5_weight]) relationship between those table is M:N through account_key. I need to calculate sum of sales_amount with applying the correct weight when going from a lower level of the hierarchy to a higher one. Not all levels for each account is valid -> each level except one can be null. I tried everything with INSCOPE, RELATED, SWITCH and SUMX but nothing worked for me. Link to the file here: PBIX I would very much appreciate any help. Thank you very much in advance.
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.