@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.
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.
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!
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!
