Hi,
Maybe someone can help me to calculate cost allocation in power pivot (dax).
I need to allocate overhead costs based on month, cost group and cost allocation type.
I have several tables with input data.
One table with costs per Account ID and month:
Other table with cost allocation information (how much from total costs should be allocated to particular cost group and how costs should be divided between real estates)
And the last table with cost allocation information:
In result I want to see table like this:
What I need to calculate:
Let’s take for example Account ID 71121234 and costs for January = 100 EUR.
Costs should be allocated between 2 cost groups (1.2. – 40% and 1.3. – 60%). 40 EUR and 60 EUR accordingly.
Next step is to divide it between Real estates.
For RE1 and cost group 1.2. and allocation type - admin it would be 40 EUR / 1000 m2 * 200m2.
For RE1 and cost group 1.3. and allocation type - Fin it would be 60 EUR / 300 m2 * 0m2.
In excel this I can calculate step by step, but how can I calculate in dax by one- two formulas? Or that is impossible?
Maybe someone can help me to calculate cost allocation in power pivot (dax).
I need to allocate overhead costs based on month, cost group and cost allocation type.
I have several tables with input data.
One table with costs per Account ID and month:
Other table with cost allocation information (how much from total costs should be allocated to particular cost group and how costs should be divided between real estates)
And the last table with cost allocation information:
In result I want to see table like this:
What I need to calculate:
Let’s take for example Account ID 71121234 and costs for January = 100 EUR.
Costs should be allocated between 2 cost groups (1.2. – 40% and 1.3. – 60%). 40 EUR and 60 EUR accordingly.
Next step is to divide it between Real estates.
For RE1 and cost group 1.2. and allocation type - admin it would be 40 EUR / 1000 m2 * 200m2.
For RE1 and cost group 1.3. and allocation type - Fin it would be 60 EUR / 300 m2 * 0m2.
In excel this I can calculate step by step, but how can I calculate in dax by one- two formulas? Or that is impossible?
