How to reallocate summed values to specific categories?

[janstrauss1]

Dear Power BI Community,

I’ve been given a data table that contains information on categorized projects and associated total budgets that I need to visualize as a pie chart like in this example. The example .pbix can be downloaded from my GitHub.

To improve the visual, I need to evenly reallocate the sum of total project budgets of the “mixed” categories (e.g. “Category1 & Category2”) to its individual single categories.

For example, the sum of 2.597.242.976,00 for “Category1 & Category2” needs to be evenly distributed between “Category1” and “Category2” so that eventually there will be only four categories left in the visual.

I was fiddling with calculated columns and measures as suggested by @v-yuta-msft in
Distributing values across categories  and by @v-rongtiep-msft  in Distributing Value into specified category but couldn’t get it to work in my case.

Thus, any help and suggestions are highly appreciated.

Many thanks in advance!

Example Data

ProjectName	ProjectTitle	MarketSegmentCategory	MarketSegmentCategoryCount	ProjectPartnerName	TotalProjectBudget
Project01	Project01_Title	Category1	1	Project01_Partner1	4499250
Project01	Project01_Title	Category1	1	Project01_Partner2	4499250
Project01	Project01_Title	Category1	1	Project01_Partner3	4499250
Project02	Project02_Title	Category2	1	Project02_Partner1	449993475
Project02	Project02_Title	Category2	1	Project02_Partner2	449993475
Project02	Project02_Title	Category2	1	Project02_Partner3	449993475
Project02	Project02_Title	Category2	1	Project02_Partner4	449993475
Project03	Project03_Title	Category3	1	Project03_Partner1	470515625
Project03	Project03_Title	Category3	1	Project03_Partner2	470515625
Project03	Project03_Title	Category3	1	Project03_Partner3	470515625
Project03	Project03_Title	Category3	1	Project03_Partner4	470515625
Project03	Project03_Title	Category3	1	Project03_Partner5	470515625
Project04	Project04_Title	Category4	1	Project04_Partner1	680300395
Project04	Project04_Title	Category4	1	Project04_Partner2	680300395
Project05	Project05_Title	Category1 & Category2	2	Project05_Partner1	1298621488
Project05	Project05_Title	Category1 & Category2	2	Project05_Partner2	1298621488
Project06	Project06_Title	Category1 & Category3	2	Project06_Partner1	743644825
Project06	Project06_Title	Category1 & Category3	2	Project06_Partner2	743644825
Project06	Project06_Title	Category1 & Category3	2	Project06_Partner3	743644825
Project07	Project07_Title	Category1 & Category3	2	Project07_Partner1	7153285
Project08	Project08_Title	Category3 & Category4	2	Project08_Partner1	2231710
Project09	Project09_Title	Category2 & Category4	2	Project09_Partner1	1999585
Project09	Project09_Title	Category2 & Category4	2	Project09_Partner2	1999585

[janstrauss1 · Accepted solution]

I just managed to solve my own problem using the following approach 😊:

1. Split column 'MarketSegmentCategory' by Deliminiter '&' in Power Query
2. Unpivoted generated columns in Power Query
3. Add calculated column to divide 'TotalProjectBudget' by the 'MarketSegmentCategoryCount' in Power Query
4. Use SUMMARIZE function (DAX) to return summary tables for my original table and the one with split categories

An updated example .pbix file can be downloaded from my GitHub.

[v-zhengdxu-msft]

Hi @janstrauss1 

Congratulations!
I'm sure there are quite a few people who have been confused by this question.
Thank you very much for sharing.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.