Pivot Table - Split Measures in column

Question & Answer
,
1

Hi All,

While creating a pivot table, I have a single row(Assume country) and column(Date) and two measures(Amount and Count)

I have a requirement to show as pivot as below

||Amount ||count||
|Country |Oct-23|Sep-23|Oct-23|Sep-23|
|ABC|100|200|300|600|
|XYZ|500|50|700|80|

But currently pivot table have option to show only like this.

||Oct-23||Sep-23||
|Country |Amount |count|Amount|count|
|ABC|100|300|200|600|
|XYZ|500|700|50|80|

Any leads on this are appreciated

Country

2

Hello @akireddyraj , welcome to the Quicksight community!

Here is how I set this up:

image
image1386×781 76 KB

Here is the second part of the filter with the second date:
image
image307×551 17.5 KB

Let me know if this is what you were looking for!

3

@duncan duncan

Thanks for your response but I am looking for quantity and sales as separate group. with in those groups I need oct 17 and sep 17 data

I mean measures first then order date,

4

To add more context

I have a requirement to show the pivot as below(measures first the column)

                Amount                                     Sales     
      Oct-2023  Sep-2023         Oct-2023        Sep-2023

USA 20 40 5 10
UK 30 80 6 20

But currently pivot table have option to show only like this.

                Oct-2023          Sep-2023
         Amount  Sales     Amount Sales

USA 20 5 40 10
UK 30 6 80 20

Could you please help on this

5

Hello @akireddyraj !

You should be able to add the Quantity and Sales from the value field well to the Columns field well to achieve this.

6

I don’t think measure can be placed in columns. even if moved to columns, each measure value will be displayed as columns and doesn’t result in the expected outcome

7

Hello @akireddyraj, if that is the case, it will require you to alter your dataset. You will need some kind of category column that will return either Sales or Amount, and have corresponding data on that row. That way you can use the category field in your columns field well and it will know how to split those values between the 2 date options. Basically, Sales data will be on a row and Amount data will be on a different row. I would recommend doing this through a UNION in the SQL statement.