Pivot table with multiple sub groups in both rows and columns

[vkarthik21]

My requirement is to have Report generated similar to what Excel performs over set of data containing multiple groups in the rows as well as columns and use the 'Reporting' functionality of Alteryx to deliver the output in say PDF format. 

Here is my current data set -

State	Demography	Grade	Cost	Qty
Telangana	Low-income	Average	180	20
Telangana	High-income	Good	150	15
Telangana	High-income	Above Average	140	90
Karnataka	Mid-income	Average	130	30
Karnataka	High-income	Above Average	125	80
Karnataka	High-income	Average	190	60
Karnataka	Low-income	Good	60	35
Kerala	Low-income	Good	90	75
Kerala	Low-income	Average	45	40
Kerala	High-income	Above Average	110	15
Delhi	Mid-income	Good	130	20

The output that I am looking for is similar to below -

Additionally at the end of each subgroup, I would need additional row for the subgroups say - Delhi - Total; Karnataka - Total ; Kerala - Total etc. 

How can this be achieved? I did lot of searching, I could see some posts linking to getting subgroup in the top - link. However, it doesn't have the same requirement as mine. Any help would be highly appreciated.

[estherb47 · Accepted solution]

Hi @vkarthik21 

It's not as easy as it should be. I thought you could do this, after a little data prep, with two joined crosstabs and a Table tool (from reporting) but the headings that default in that view are totally wonky.

I built the pivot view from scratch, using tables and titles.

Incidentally, you can assign an order to the fields in the pivot by prepending a number (1,2,3) to produce the order you want. Remove them later with a RegEx_Replace in a formula tool.

Not the prettiest output, so you might play a bit more with the tables. I might too. 

[estherb47]

Whoops! Forgot the Render tool.

[vkarthik21]

Hi Esther,

Thank you so much for the quick response. I did play around with cross tab and pivot but it wasn't resulting in the output I needed. Your solution is perfect. 

I feel Alteryx would implement this as an out-of-the box feature as this is a pretty standard reporting requirement and all the reporting tools handle it very easily.

Regards,

Karthik