Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
Hello,
Anyone who could help me on this?
Thank you!
Kind regards
Raphaël
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |