Sub-allocation of categories

[davidnsquires]

Hi, I'm not sure if this is possible or not. We have a set of data that is allocated to business units. For the purposes of this post let's say they are the days Monday to Friday. However, in some cases an entry may be allocated to multiple days.

For the output we want the graph to just be split into the five categories (I.e. Monday to Friday). If an entry was "Monday and Tuesday", for example, we would want it 50% allocated to Monday and 50% to Tuesday. If there were three we'd want it allocated 1/3 to each and so on. Is there a way to do that?

[Anonymous · Accepted solution]

@ryan_mayu , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:


@davidnsquires , if this sample data is structurally different from the one you are using, please do not hesitate to mention it and I will modify it!

I created sample data based on your needs and established relationships between them.

Select Split column for the Weekday column of the Product table in Power Query.

Select three columns of Weekday to perform the Unpivot operation.

Delete the Attribute column and the cleaned data is as shown in the figure below.

Create two calculated columns:

Count = COUNTROWS(FILTER('Product', 'Product'[ProductID] = EARLIER('Product'[ProductID])))
Percentage = 1 / [Count]

The page effect is as shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ryan_mayu]

could you pls provide some sample data?