Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experts,
I have a pivot table which looks like below,
BCOD | MO_NM | CLA_CODE | EFF_MONTH | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B100 | MOTOR | M3 | 148916 | 158661 | 164360 | 158743 | 158506 | 140203 | 186970 | 202715 | 155078 | 178589 | |
M4 | 37313 | -8892 | 31127 | 22245 | 38612 | 31066 | 35182 | 91030 | 71030 | 21120 | |||
MC | 2117618 | 2175753 | 1913049 | 1858664 | 1975620 | 1798944 | 2124056 | 2254747 | 2474338 | 2893513 | |||
Total | 2303847 | 2325522 | 2108536 | 2039653 | 2172738 | 1970213 | 2346209 | 2548491 | 2700447 | 3093222 | |||
NON_MOTOR | EN | 116400 | -66000 | 34315 | 58800 | 30000 | 301550 | 34000 | 32095 | 26650 | 79580 | ||
FR | 145313 | 24567 | 119970 | 84460 | -6379 | 145310 | 20684 | 25670 | 240172 | 8142 | |||
HH | 801 | 35125 | 32875 | 92325 | 23690 | -105220 | 46977 | -7215 | -17034 | 10641 | |||
MS | 48652 | 160564 | 65028 | 156041 | 218236 | 178873 | 219810 | 178535 | -21717 | -18022 | |||
PP | 43439 | 38965 | 34565 | 17289 | 21726 | 26677 | 37848 | 43033 | 29476 | 32494 | |||
SS | 52700 | 28200 | 3700 | -28200 | 28200 | 56400 | -84600 | 47960 | 29960 | 40460 | |||
TC | 46761 | 145345 | 189729 | 134618 | 189818 | 49486 | 279052 | 166747 | 230740 | 255439 | |||
TT | 3300 | - | - | - | - | - | - | - | 6250 | 43450 | |||
Total | 457366 | 366766 | 480182 | 515333 | 505291 | 653076 | 553772 | 486825 | 524497 | 452183 |
In this presentation only expression is SUM(AMOUNT_2016)
My problem is how can i modify above expression to achieve percentages from the total instead of above figures. then the total must be 100.
Rgds,
Priyantha.
Hi,
Try this expression:
SUM(AMOUNT_2016)/SUM(Total<CLA_CODE>AMOUNT_2016)
HTH
Sushil
It is important also to determine dimensionality - so the question is whether your percentage should be calculate from subtotal on CLA_CODE or maybe MO_NM level or on any other level. Then ultimetly change value in those brackets
<CLA_CODE> as per Sushil's post
regards
Lech
Dear Sushi,
I modified with your assistance and got the following expression.
ROUND(SUM(AMOUNT_2016)/SUM(Total<MO_NM>AMOUNT_2016),0.0001)
But expected output does not achieved.
Look at the resulted output.
BCOD | MO_NM | CLA_CODE | EFF_MONTH | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B100 | MOTOR | M3 | 0.0063 | 0.0067 | 0.0070 | 0.0067 | 0.0067 | 0.0059 | 0.0079 | 0.0086 | 0.0066 | 0.0076 | |
M4 | 0.0016 | -0.0004 | 0.0013 | 0.0009 | 0.0016 | 0.0013 | 0.0015 | 0.0039 | 0.0030 | 0.0009 | |||
MC | 0.0897 | 0.0922 | 0.0810 | 0.0787 | 0.0837 | 0.0762 | 0.0900 | 0.0955 | 0.1048 | 0.1226 | |||
Total | 0.0976 | 0.0985 | 0.0893 | 0.0864 | 0.0920 | 0.0835 | 0.0994 | 0.1079 | 0.1144 | 0.1310 | |||
NON_MOTOR | EN | 0.0233 | -0.0132 | 0.0069 | 0.0118 | 0.0060 | 0.0604 | 0.0068 | 0.0064 | 0.0053 | 0.0159 | ||
FR | 0.0291 | 0.0049 | 0.0240 | 0.0169 | -0.0013 | 0.0291 | 0.0041 | 0.0051 | 0.0481 | 0.0016 | |||
HH | 0.0002 | 0.0070 | 0.0066 | 0.0185 | 0.0047 | -0.0211 | 0.0094 | -0.0014 | -0.0034 | 0.0021 | |||
MS | 0.0097 | 0.0321 | 0.0130 | 0.0312 | 0.0437 | 0.0358 | 0.0440 | 0.0357 | -0.0043 | -0.0036 | |||
PP | 0.0087 | 0.0078 | 0.0069 | 0.0035 | 0.0043 | 0.0053 | 0.0076 | 0.0086 | 0.0059 | 0.0065 | |||
SS | 0.0105 | 0.0056 | 0.0007 | -0.0056 | 0.0056 | 0.0113 | -0.0169 | 0.0096 | 0.0060 | 0.0081 | |||
TC | 0.0094 | 0.0291 | 0.0380 | 0.0269 | 0.0380 | 0.0099 | 0.0559 | 0.0334 | 0.0462 | 0.0511 | |||
TT | 0.0007 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0013 | 0.0087 | |||
Total | 0.0916 | 0.0734 | 0.0961 | 0.1032 | 0.1012 | 0.1307 | 0.1109 | 0.0975 | 0.1050 | 0.0905 |
with this calculation total raw must have the 1.0000 as the answer in both Motor and Non_Motor
Can you please find the mistake i have done here.
Hi,
From what i am counting the total is 1 for both - so there is no mistake, unless you want total for each month as well, dont you?
This would be then different granularity <CLA_CODE>
hth
Lech
Hi Priyantha,
Please have a look at the attached QV.
Hi,
I quess this is what you wanted:
=sum(AMOUNT)/SUM(Total<MO_NM, EFF_MONTH>AMOUNT)
thath gives you this result:
If you need different calculation, maybe provide and XLS example what you're trying to achieve.
kind regards
Lech