Forum Discussion
Use a dynamic distribution key
Hello
I would like to know how to build a chart where non allocated costs will be moved to countries based on their turnover weight.
For example I have this :
| Name | Amount | Analytic |
| Revenue | 10.000 | USA |
| Revenue | 50.000 | UK |
| Revenue | 10.000 | France |
| Cost of sales | -8.000 | USA |
| Cost of sales | -20.000 | UK |
| Cost of sales | -3.000 | France |
| G&A | -5.000 | G&A |
where I would like to move -5 k€ of G&A on countries splitted on a dynamic calculation based on turnover and in this case :
USA : 12k/72k = 16,6%
UK : 50 k/72k = 69,4%
France : 10k/72k = 14%
So the expected result is :
| Name | Amount | Analytic |
| Revenue | 12.000 | USA |
| Revenue | 50.000 | UK |
| Revenue | 10.000 | France |
| Cost of sales | -8.000 | USA |
| Cost of sales | -20.000 | UK |
| Cost of sales | -3.000 | France |
| G&A | -833 | USA |
| G&A | -3.472 | UK |
| G&A | -694 | France |
What do you advise me to achieve this?
Thank you very much!
Kind regards
Raphaël
6 Replies
Hello AlB
Thank you very much for your second solution, unfortunately I think my example was a little too simple compared to the reality and I can't apply your solution on my dataset. I made a PBIX with something closer to my data and the expected result. It would be nice if you could have a look on this.
https://drive.google.com/file/d/1GRWKyi3ZpaPrDElfJ3unfK9-kzJW5AiM/view?usp=sharing
Thank you!
Kind regards
Raphaël
Hello,
Anyone who could help me on this?
Thank you!
Kind regards
Raphaël
As it seems complicated to transfer a PBIX file, I have reproduced the model on Power Pivot.
https://drive.google.com/file/d/1fqun9xrlB4PwU_5C3EgTD2doVNPUTJJD/view?usp=sharing
