Hi Everyone,
This is a tough one I cannot manage to solve, so I'll try to explain short and sweet and thanks for any input in advance.
In short, I'm trying to redistribute the company sales (internal) values between 3 subgroups, based on how a customer locally distributed the purchased goods. The problem is that there is no direct relationship, since the customer uses internal references and some of them is not even an existing account in the internal system. This is 1 customer group, and there are other customers as well.
The redistribution should happen on month and subgroup level, which means on row level I get a % from the division of sales value and the total of the sales for that group in the given month (calculate(sum(sales),filter(subgroup = earlier(subgroup),filter(month = earlier(month))).
Then this % should split the sales from the internal sales table (that is also on a subgroup and month level), so for example, if customer X in subgroup 1 had 2% (external) in 2018 JAN then I'd like to multiply (internal) Y sales value of subgroup 1 in 2018 JAN by 0.02
external performance (based on external sales) for account X by subgroup by month = 2%
internal performance by subgroup by month = 1000
required value = 1000 * 0.02 = 20
Below are some related snapshots and I can also share a sample PBIX file, if needed. The live data is also arranged like this, apart from 1 difference - customer sales is filtered by the date table, the relationship is not bi-directional.
So I'm a bit stuck with this. I managed to get the % values (by month by subgroup), but I'm not sure how could I split up the internal sales values using the percentages, as there is no direct relationship.
I really appreciate any help and thanks for reading.
