Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I am actually looking for additional columns in Pivot table so that i can show overall Total for 12 Months , Average and Percentage difference like below
Can any one has solution for this as i am aware you can not add additional columns, the other work around i am doing is having a separate straight table for 12 months total, average and percentage differences but i actually wanted to that in pivot table itself so if user wants to import he can import the entire 12 month data and total, avg and percentage diff
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | |||
Test | Test | Test | Test | Test | Test | Test | Test | Test | Test | Test | Test | Test | 12Month Total | 12Mth Average | |
A | 29 | 31 | 37 | 49 | 65 | 65 | 53 | 63 | 56 | 36 | 37 | 89 | 49 | 630 | 52.50 |
B | 78 | 78 | 76 | 25 | 39 | 57 | 72 | 30 | 36 | 64 | 26 | 13 | 37 | 553 | 46.08 |
C | 55 | 79 | 60 | 78 | 79 | 73 | 98 | 59 | 42 | 26 | 78 | 20 | 91 | 783 | 65.25 |
D | 83 | 69 | 77 | 52 | 85 | 87 | 81 | 63 | 64 | 57 | 58 | 75 | 23 | 791 | 65.92 |
E | 40 | 86 | 68 | 12 | 64 | 83 | 99 | 25 | 89 | 41 | 98 | 29 | 58 | 752 | 62.67 |
F | 74 | 30 | 96 | 40 | 24 | 22 | 63 | 26 | 66 | 75 | 87 | 71 | 97 | 697 | 58.08 |
G | 38 | 64 | 58 | 82 | 22 | 99 | 41 | 91 | 11 | 94 | 83 | 60 | 60 | 765 | 63.75 |
H | 67 | 97 | 19 | 53 | 90 | 90 | 55 | 46 | 24 | 74 | 43 | 45 | 77 | 713 | 59.42 |
I | 99 | 30 | 61 | 73 | 84 | 33 | 48 | 43 | 44 | 93 | 14 | 31 | 98 | 652 | 54.33 |
J | 27 | 12 | 39 | 53 | 10 | 41 | 93 | 60 | 57 | 20 | 99 | 40 | 83 | 607 | 50.58 |
K | 73 | 33 | 48 | 37 | 83 | 39 | 85 | 34 | 16 | 59 | 16 | 86 | 12 | 548 | 45.67 |
L | 36 | 94 | 62 | 27 | 68 | 100 | 92 | 91 | 77 | 33 | 34 | 11 | 28 | 717 | 59.75 |
M | 41 | 36 | 92 | 22 | 14 | 72 | 84 | 13 | 31 | 96 | 25 | 49 | 75 | 609 | 50.75 |
N | 40 | 42 | 29 | 74 | 37 | 94 | 54 | 79 | 49 | 71 | 56 | 73 | 23 | 681 | 56.75 |
O | 67 | 55 | 80 | 45 | 33 | 55 | 59 | 45 | 63 | 68 | 58 | 10 | 92 | 663 | 55.25 |
P | 52 | 85 | 40 | 21 | 92 | 20 | 80 | 62 | 81 | 99 | 10 | 47 | 36 | 673 | 56.08 |
Q | 69 | 25 | 70 | 98 | 74 | 17 | 98 | 10 | 64 | 73 | 90 | 74 | 57 | 750 | 62.50 |
R | 90 | 34 | 32 | 43 | 16 | 82 | 65 | 79 | 89 | 63 | 15 | 30 | 28 | 576 | 48.00 |
S | 41 | 25 | 30 | 63 | 41 | 10 | 54 | 79 | 81 | 85 | 43 | 87 | 35 | 633 | 52.75 |
T | 24 | 71 | 23 | 56 | 30 | 32 | 11 | 26 | 14 | 52 | 76 | 19 | 40 | 450 | 37.50 |
Thanks
Kushal Thakral
The simplest way is to add a further horizontal dimension above your month-dimension (maybe Year) and then enabling partial sums for both horizontal dimensions and query them within your expression with:
if(secondarydimensionality() = 0, AvgExp, SumExp)
Otherwise you will need to create extra field-values for your month-dimension like sum and avg (maybe an additionally field only for these purpose) and query then which value your extended dimension had:
if(Month = 'avg', AvgExp, if( Month = 'sum', TotalSumExp, SumExp))
- Marcus