Forum Discussion
Exception for sub-total
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,
3 Replies
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 % ?
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
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/
