Exception for sub-total

[IF]

Hi,
I want to re-do a table that was done in excel. I have the following data:

XX	YY	A	B	C	D	E	F	G	I	J	M	P	R	PP	RR
35353	2M	236	23	4	6	7	5	5	4	6	9	22	11	9%	5%
35353	3M	233	21	2	5	2	5	5	2	4	8	14	9	6%	4%
35353	6M	251	33	4	6	7	5	5	4	6	9	22	11	9%	4%
35353	7M	263	43	2	5	2	6	5	2	4	8	15	9	6%	3%
35355	1M	257	46	4	6	7	5	5	4	6	9	22	11	9%	4%
35355	2M	255	66	2	5	2	5	5	2	4	8	14	9	5%	4%
35355	3M	244	55	4	6	7	5	5	4	6	3	22	11	9%	5%
35355	5M	352	58	2	5	2	5	5	2	4	8	14	9	4%	3%
35360	1M	241	59	4	6	9	5	5	4	6	9	24	11	10%	5%
35360	2M	251	35	2	9	2	8	5	2	4	1	21	9	8%	4%
35360	4M	264	53	4	6	7	5	9	4	6	9	22	15	8%	6%
35360	7M	231	38	2	5	2	5	8	2	4	2	14	12	6%	5%
35380	1M	222	32	4	6	7	5	5	4	6	9	22	11	10%	5%
35380	2M	230	39	2	5	2	5	5	2	4	9	14	9	6%	4%
35380	3M	229	45	9	6	7	5	5	6	6	3	24	11	10%	5%
35380	4M	228	76	2	5	2	5	5	2	4	8	14	9	6%	4%
35380	5M	225	89	6	6	7	5	5	4	6	9	22	11	10%	5%

Based on this data, I would like to display similar table as it is show below. This  table should be done for each XX value. I only provided an example for the first one.

XX	YY	A	B	C	PP	RR
35353	2M	236	23	4	9%	5%
35353	3M	233	21	2	6%	4%
35353	6M	251	33	4	9%	4%
35353	7M	263	43	2	6%	3%
			34		3%
Total(N)		983	86	12	26%	16%
Total(%)			8.7%	14.0%	2.2%	60.7%

For Column B, I must subtract total of M value for each XX column(category). For example; The M values for 35353 is 9+8+9+8=34. Only for B column the logic should be 23+21+33+43-34=86. PP column also should have similar logic with percentage. 3% is coming from 34/983.

I used matrix. I can have subtotals but not the sub-total percentage and subtotal(N) together. Can anyone help me for this issue...

All the best,

[amitchandak]

@IF , not very clear

But it would be like

New B = Sum(Table[B])- Sum(Table[M])

% = Divide([New B], Sum(Table[B])) , what is denominator for % ?

[IF]

Hi,

Thanks for the answer. Sorry for being unclear. Based on the data, I want to have the following table:

		A	B	C	PP	RR
35353	2M	236	23	4	9%	5%
35353	3M	233	21	2	6%	4%
35353	6M	251	33	4	9%	4%
35353	7M	263	43	2	6%	3%
			34		3%
Total(N)		983	86	12	26%	16%
Total(%)			8.7%	1.2%

8.7% is 86/983  (SUM(B)-SUM(M))/SUM(A) However, I don't know how to show it in a table or in a similar format that I provided. This should be series for all XX values. Actually a matrix or a table is fine as well. Also as you see in Total(N), some of the totals are exclusion of SUM(M) and some of them don't include. When I have a table or matrix, it gives total with the same logic for all columns, I belive. At least, I don't know.

All the best

[amitchandak]

@IF , I doubt you can create a table which shows %total below total. The best you can do is change total by using is filtered or is in scope or has one value.

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/