Use a dynamic distribution key

[Raph]

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

[AlB · Accepted solution]

Hi @Raph 

See the attached file for a possible solution.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Raph]

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

[Raph]

Hello,

Anyone who could help me on this?

Thank you!

Kind regards

Raphaël

[Raph]

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

[AlB · Accepted solution]

@Raph 

In fact, here is a simpler version without the auxiliary table. See the attached file

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Raph]

Thank you very much @AlB  !

Really helpful.

Kind regards

Raphaël

[AlB · Accepted solution]

Hi @Raph 

See the attached file for a possible solution.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers