Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Sub & Grand Totals at the column and row level

ccano
8 - Asteroid

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 DateTeam RegionsCurrent11- 7 Days Late8- 15 Days Late16- 30 Days Late31- 45 Days Late46- 60 Days Late61- 90 Days LateOver 90 Days LateGrand Total
30-Jun-21South 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-21EMEA($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-21Asia($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-21North 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-21Central 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-21APAC$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-21Total($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-21South 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-21EMEA($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-21Asia($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-21North 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-21Central 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-21APAC($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-21Total($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-21South 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-21EMEA($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-21Asia($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-21North 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-21Central 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-21APAC($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-21Total($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 TotalTotal($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)
3 REPLIES 3
KarolinaRoza
11 - Bolide

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

T_Willins
14 - Magnetar
14 - Magnetar

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.

ccano
8 - Asteroid

Thank you so much for the support here! I greatly appreciate it! It worked well, added a few finishing touches 🙂 

Labels