How to show percentage by category and subcategory separately in a matrix table

[LahiruFernando]

Dear All,

I have below concern and please do assit on this. I have a data table similart to tale no 1. which has main category and sub categories under each main category for each month and their respective values.

Category	SubCategory	Month	Value
A	a	01-Jan-24	100
A	b	01-Jan-24	200
A	c	01-Jan-24	100
B	a	01-Jan-24	350
B	b	01-Jan-24	250
A	a	01-Feb-24	250
A	b	01-Feb-24	150
A	c	01-Feb-24	200
B	a	01-Feb-24	350
B	b	01-Feb-24	150

by using the row data it is very easy to create the table below using matrix table,

Category	Sub category	01-Jan-24	01-Feb-24	Total
A		400	600	1000
	a	100	250	350
	b	200	150	350
	c	100	200	300
B		600	500	1100
	a	350	350	700
	b	250	150	400
Total		1000	1100	2100

but what i want it to generate below table as the final result which when for each subcategory, the percentage should calculate within the main category and sum up to 100% within the main category. where the total percentage of main categories sum up to 100% seperately for each month.

Category	01-Jan-24	01-Feb-24	Total
A		40%	55%	48%
	a	25%	42%	35%
	b	50%	25%	35%
	c	25%	33%	30%
B		60%	45%	52%
	a	58%	70%	64%
	b	42%	30%	36%
Total		100%	100%	100%

much appreciated your solutions towards this.
Thanking in advance.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[LahiruFernando]

@Ashish_Mathur ,

Thank you so much for the response, this helps with the subcategory %. can you help me to show the maincategory level % as below without changing the what i highlighted as correct.
example : what is ticked is correct, need to change the main category A=40%, B=60% for the month of Jan

[Ashish_Mathur · Accepted solution]

You are welcome.  PBI file attached.

Hope this helps.

[LahiruFernando]

@Ashish_Mathur ,
Thank you so much. this works perfrectly.

[Ashish_Mathur]

You are welcome.

[LahiruFernando]

@Ashish_Mathur ,

Thank you so much for the response, this helps with the subcategory %. can you help me to show the maincategory level % as below without changing the what i highlighted as correct.
example : what is ticked is correct, need to change the main category A=40%, B=60% for the month of Jan

[Tahreem24]

@LahiruFernando , You can use the inbuilt functionality of "Percent of column total". Just click "Down arrow "on your value column and choose the below option so you'll get the expected output.

[LahiruFernando]

@Tahreem24 ,
Thank you so much for your solution, I have tried this before and it will give me the main category level percentage split correctly sum up to 100%. but what I want is at the same time to calculate the subcategory level % within the category, where the total % within main category of subcategories should sum up to % as well. so then I can see what is the contribution form each sub category to the main category. 

if you will be able to generate, please kindly assist
Thank you