Pivot Table Calculations with filters

To explain my problem I’m using the below sample dataset.

1 20230509 100 A F 1
1 20230509 150 B F 1
2 20230509 200 D M 2
2 20230509 120 E M 2
1 20230510 130 A F 1
2 20230510 230 A M 2
3 20230510 220 C F 3

First I want to get the BRAND wise consumption% as below for both days, date wise.

DATE BRAND Sum of DRINKS Consumption %
20230509 A 190 21.6
20230509 B 370 42.0
20230509 D 200 22.7
20230509 E 120 13.6
20230510 A 360 62.1
20230510 C 220 37.9

Secondly I want to get BRAND wise consumption% for both days aggregrated.

BRAND Sum of DRINKS Consumption %
A 550 37.7
B 370 25.3
C 220 15.1
D 200 13.7
E 120 8.2

Appreciate if someone can help me out here.

@PandaDH Thanks for your question. If you don’t get a reply soon from one of our community members, we’ll reach out to our internal experts on Tuesday to see if we can get a reply for you.

Hope you had a great weekend!

1 Like

Thank you @Kristin, hoping to get a reply soon :slightly_smiling_face:

Try this:

To achieve the desired calculations in Amazon QuickSight, you can follow these steps:

Step 1: Import the Sample Dataset

  • Start by importing the sample dataset into QuickSight. Make sure the columns are correctly mapped during the import process.

Step 2: Create a Pivot Table

  • Create a new analysis in QuickSight and add a new sheet.
  • Drag and drop the “BRAND” column into the Rows shelf.
  • Drag and drop the “DATE” column into the Columns shelf.
  • Drag and drop the “DRINKS(L)” column into the Values shelf. Change the aggregation of this field to sum (right-click on the field and select “Summarize” > “Sum”).
  • Your pivot table should now display the sum of drinks for each brand and date combination.

Step 3: Add a Calculation for Consumption %

  • Click on the “Analysis” menu and select “Create Calculated Field”.
  • Name the calculated field “Consumption %” and enter the following formula: [Sum of DRINKS] / sumOver([Sum of DRINKS] by [BRAND]) * 100 This formula calculates the percentage of each brand’s drinks out of the total drinks for that brand across all dates.

Step 4: Add a Totals Row for Daily Consumption %

  • Right-click on the “DATE” field in the Columns shelf and select “Add Totals > Row Totals”.
  • This will add a totals row at the bottom of the pivot table, showing the daily consumption percentages for each brand.

Step 5: Add a Totals Column for Aggregated Consumption %

  • Right-click on the “BRAND” field in the Rows shelf and select “Add Totals > Column Totals”.
  • This will add a totals column at the end of the pivot table, showing the aggregated consumption percentages for each brand across all dates.

Your final pivot table should resemble the desired output you provided in your question.

Note: The calculation formula assumes that the aggregation for the “Sum of DRINKS” field is set to sum. Adjust the formula accordingly if you have used a different aggregation method.

Hope this helps. Let us know. Thanks!

Hi @rickm , thanks for the reply.

I managed to do upto 3rd action point and need your support to complete the 4th and 5th points.

Hi @PandaDH, You an easily achieve percent of total using pivot view with quicksight without creating new calculated columns. Please refer to below video. If this meets your requirment please help the community out by marking this answer as "Solution!
2023-05-31_14-58-17 (1)


1 Like

Hi @PandaDH Did @Karthik_Tharmarajan’s solution answer your question? If so, could you help the community out by clicking the check box under the reply to mark it as solved? Thanks! :slight_smile:

1 Like

Thanks @Karthik_Tharmarajan, this solution worked well!

1 Like