I have multiple measures (in column format) with different dimensions (at the row level).
I need to obtain the subtotal at the Date Level (as a row) and I'd like to obtain the Grand totals towards the later portion of the pivot. Here is a visual representation of the end goal.
Day of Ar Date | Team Regions | Current1 | 1- 7 Days Late | 8- 15 Days Late | 16- 30 Days Late | 31- 45 Days Late | 46- 60 Days Late | 61- 90 Days Late | Over 90 Days Late | Grand Total |
30-Jun-21 | South America | ($2,527.56) | ($2,042,761.49) | ($423,848.67) | ($101,282.10) | ($44,710.49) | ($9,805.61) | ($84,037.67) | ($504,818.61) | ($3,211,264.64) |
30-Jun-21 | EMEA | ($159,051.99) | ($2,693,318.13) | ($1,060,875.30) | ($365,756.26) | ($212,679.75) | ($447,517.25) | ($162,339.01) | ($2,131,660.77) | ($7,074,146.47) |
30-Jun-21 | Asia | ($37,908.37) | ($10,445,027.57) | ($1,147,481.37) | ($1,389,203.26) | ($154,700.41) | ($311,564.07) | ($564,396.16) | ($2,087,629.04) | ($16,100,001.88) |
30-Jun-21 | North America | ($324,035.17) | ($3,008,641.04) | ($262,191.10) | ($339,456.49) | ($105,193.01) | ($149,378.32) | ($205,761.42) | ($1,000,355.38) | ($5,070,976.76) |
30-Jun-21 | Central America | ($67,277.32) | ($45,502,366.42) | ($1,156,327.98) | ($989,640.56) | ($288,463.24) | ($126,491.50) | ($168,807.42) | ($931,440.06) | ($49,163,537.18) |
30-Jun-21 | APAC | $0.00 | ($1,364,716.86) | ($43,899.50) | ($119,732.16) | ($12,524.13) | ($4,253.35) | ($18,580.66) | ($406,164.42) | ($1,969,871.08) |
30-Jun-21 | Total | ($590,800.41) | ($65,056,831.51) | ($4,094,623.92) | ($3,305,070.83) | ($818,271.03) | ($1,049,010.10) | ($1,203,922.34) | ($7,062,068.28) | ($82,589,798.01) |
29-Jun-21 | South America | ($146,189.40) | ($1,333,889.47) | ($269,312.67) | ($90,801.02) | ($44,765.36) | ($9,805.61) | ($88,496.27) | ($500,360.01) | ($2,337,430.41) |
29-Jun-21 | EMEA | ($1,116,082.49) | ($1,646,947.29) | ($1,081,163.41) | ($333,102.29) | ($212,679.75) | ($451,157.71) | ($215,928.20) | ($2,130,497.17) | ($6,071,475.82) |
29-Jun-21 | Asia | ($7,588,615.85) | ($2,028,998.35) | ($1,322,280.86) | ($941,622.45) | ($154,700.41) | ($407,587.38) | ($473,162.67) | ($2,084,992.35) | ($7,413,344.47) |
29-Jun-21 | North America | ($642,656.27) | ($2,423,385.69) | ($271,955.95) | ($318,420.47) | ($105,243.01) | ($154,665.14) | ($209,918.60) | ($993,136.34) | ($4,476,725.20) |
29-Jun-21 | Central America | ($30,121,799.46) | ($17,164,022.08) | ($988,288.49) | ($944,110.70) | ($288,463.24) | ($136,568.25) | ($158,730.67) | ($931,440.06) | ($20,611,623.49) |
29-Jun-21 | APAC | ($612,281.69) | ($737,690.48) | ($38,155.68) | ($119,732.16) | ($12,524.13) | ($4,253.35) | ($18,580.66) | ($406,164.42) | ($1,337,100.88) |
29-Jun-21 | Total | ($40,227,625.16) | ($25,334,933.36) | ($3,971,157.06) | ($2,747,789.09) | ($818,375.90) | ($1,164,037.44) | ($1,164,817.07) | ($7,046,590.35) | ($42,247,700.27) |
28-Jun-21 | South America | ($470,500.49) | ($2,667,816.48) | ($352,520.24) | ($117,389.88) | ($59,880.92) | ($25,866.11) | ($57,807.57) | ($520,408.15) | ($3,801,689.35) |
28-Jun-21 | EMEA | ($819,459.32) | ($1,710,233.95) | ($1,068,767.60) | ($333,288.96) | ($273,143.32) | ($393,603.50) | ($261,343.74) | ($2,101,037.33) | ($6,141,418.40) |
28-Jun-21 | Asia | ($1,818,043.35) | ($2,229,176.20) | ($1,247,817.21) | ($1,054,964.27) | ($156,134.67) | ($407,587.38) | ($500,146.17) | ($2,058,008.85) | ($7,653,834.75) |
28-Jun-21 | North America | ($1,658,898.51) | ($2,176,271.02) | ($273,995.22) | ($353,504.27) | ($119,371.80) | ($197,498.25) | ($176,070.83) | ($991,623.87) | ($4,288,335.26) |
28-Jun-21 | Central America | ($5,155,942.24) | ($15,132,708.89) | ($433,684.97) | ($972,764.19) | ($258,912.34) | ($134,889.25) | ($230,195.42) | ($857,591.04) | ($18,020,746.10) |
28-Jun-21 | APAC | ($186,618.12) | ($707,353.96) | ($50,589.78) | ($125,804.12) | ($13,197.47) | ($8,278.63) | ($23,332.03) | ($401,413.05) | ($1,329,969.04) |
28-Jun-21 | Total | ($10,109,462.03) | ($24,623,560.50) | ($3,427,375.02) | ($2,957,715.69) | ($880,640.52) | ($1,167,723.12) | ($1,248,895.76) | ($6,930,082.29) | ($41,235,992.90) |
Grand Total | Total | ($50,927,887.60) | ($115,015,325.37) | ($11,493,156.00) | ($9,010,575.61) | ($2,517,287.45) | ($3,380,770.66) | ($3,617,635.17) | ($21,038,740.92) | ($166,073,491.18) |
Solved! Go to Solution.
hi @ccano
I prepared workflow which I hope meets your needs.
Mainly this is combination of summary tools and unions.
Please let me know if it is fine.
Regards,
Karolina
HI @ccano,
In addition to the solution by @KarolinaRoza below, you should also look at the CReW Macro Add Totals. If you are not familiar with or have not downloaded the CReW macros see more information at this website: http://www.chaosreignswithin.com/p/macros.html
Not only are there the macros to download, but a blog with lots of great reading.
Thank you so much for the support here! I greatly appreciate it! It worked well, added a few finishing touches 🙂