Hi, I am displaying a table looking like the following:
|Maturity||Sales product1||Sales product2||Sales product3|
That I would like to transpose to look like this:
|Maturity||1 year||2 year||3 year||4 year||5 year||6 year|
Any suggestions to do this please. Thanks very much.
Create a pivot table:
as a dimension:
=ValueList('Sales Product1','Sales Product2','Sales Product3')
as a measure:
if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product1', sum([Sales product1]),
if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product2', sum([Sales product2]),
if(ValueList('Sales Product1','Sales Product2','Sales Product3')='Sales Product3', sum([Sales product3]))))
add a line:
As Kaan rightly suggested, you can do it by simply creating a pivot chart and transposing your measures and Dimension.
But an alternate way to do this is in the script as well. PFA images for your reference.
Thanks Kaan. Can you please explain how to convert an existing table to pivot table ?
I have tried to create a new pivot table and flipped the columns into rows but did not get the right results. the reason being that the Sales Product columns don’t exist already in the tables, but I create them on the fly using formulas like
=sum(if([Product]='Product1', [Sales])) for Sales product1 column.
=sum(if([Product]='Product2', [Sales])) for Sales product2 column
=sum(if([Product]='Product3', [Sales])) for Sales product3 column
You can just drag a Pivot Table over a Table as select Convert if you want to convert it but you may have to review the default design and may not get exactly what you want right away.
To create from scratch just try something simpler:
Measure: Sales and select Sum
Then Add Data (Column) and select Year
Then, if you want, you can restrict Sales to specific products by editing the Measure and changing it from
I hope this helps,