Creating a table with row and column headers

[Dheeru28]

I have data (input) on which I need to perform some calculations to get the result that I want (output).

Input:

Sales	Store	Product	Category	Quantity
500	A	1	Inhouse	40
205	B	2	External	25
360	C	1	Inhouse	35
400	D	2	Inhouse	30
155	E	3	External	10
268	F	4	Inhouse	20
306	G	1	External	25
478	H	3	Inhouse	28
121	I	4	Inhouse	12

Output:

	Product 1	Product 2	Product 3	Product 4
Total Sales
Total Quantity
Average Quanity
Inhouse
Inhouse as %ge of total

Please note that the above data is just a sample. Actual data is quite large.

Can you help me get the above output from the given input?

Thanks in advance!

[FinnCharlton · Accepted solution]

Hi @Dheeru28 , here is one way to do this:

First, we use summarise and filter tools to generate the aggregate values we need. We then use a formula tool to calculate the 'Inhouse Sales as % of total'. The last two tools transform the data into the structure you need. Hope this helps!

[IR_2021 · Accepted solution]

Hey,

This is a way to do this. I hope it will work for you. 

[Dheeru28]

@FinnCharlton and @IR_2021 Thank you very much. It works.

There is one more thing: If I want to create a new field as a row header (in row 6) by multiplying total sales(1st row) with total quantity(2nd row) for each of the products. How do I go about doing that?

In actual data, there are multiple such calculations to be done. So is there a way to do it easily?

Also, is it possible to create pivot tables in Alteryx as we can do in excel?

[FinnCharlton]

@Dheeru28 , you'll want to think about how your data is structured. It is very easy to multiply different fields (columns) in Alteryx, but not easy to multiply values from different rows. I would recommend you structure your data so that each metric (Sales, Quantity, Inhouse Sales) is a COLUMN, and each product is a ROW. This is the data structure at this stage of my above answer:

You can then easily use a formula tool to do your calculations. If you really need your data in your initial structure, you can change it after you do all your calculations.

As for Pivot Tables, you can accomplish the same using combinations of the Transpose, CrossTab and Filter tools. It takes a bit of practice.

[Dheeru28]

Thanks a lot, @FinnCharlton !!

I actually need the output in that initial structure.

I will try doing that way and see if I am able to get what I want.