Total function

Q&A
, , , , , , ,
1

Hello ,

I would like to display the sum or total of the column TotalObUnits (i.e., 3,142,755.39) for week 24 in the RatePack column, either on the line where process_name = Pack Support or across the entire row. I have attempted to create several calculated fields and modify the data, but without success. Do you have any ideas on how I could achieve this?

image

2

Hi @juditliz, you can use calculated fields to aggregate the data and display the total in the specific column. Here’s a step-by-step guide to help you accomplish this:

Step 1: Create a Calculated Field for TotalObUnits

  1. Open your dataset in QuickSight.
  2. Create a new calculated field. Name it something like TotalObUnits_Week24.
  3. Use the following formula:

Pseudocode (Syntax may vary)

ifelse({week_number} = 24, {TotalObUnits}, 0)

This formula ensures that the TotalObUnits values are considered only for week 24.

Step 2: Aggregate the Calculated Field

  1. Create another calculated field to sum the values from the previous step:

Pseudocode (Syntax may vary)

sumOver({TotalObUnits_Week24}, [{week_number}])

This will give you the total of TotalObUnits for week 24.

Step 3: Display the Total in the Desired Column

  1. Add the calculated field to your analysis.
  2. In your visual, place the calculated field (created in Step 2) in the RatePack column.
  3. Adjust the visual settings to ensure it displays the total only for the row where process_name = Pack Support. You can do this by applying a filter or using conditional formatting.

Example Implementation:

  1. Calculated Field for Week 24 Total:

Pseudocode (Syntax may vary)

TotalObUnits_Week24 = ifelse({week_number} = 24, {TotalObUnits}, 0)
  1. Aggregated Calculated Field:

Pseudocode (Syntax may vary)

SumTotalObUnits_Week24 = sumOver({TotalObUnits_Week24}, [{week_number}])
  1. Add SumTotalObUnits_Week24 to your visual.
  2. Apply Filters and Conditional Formatting as needed to ensure the total displays only for Pack Support or across the desired row.

By following these steps, you should be able to display the sum of TotalObUnits for week 24 in the RatePack column, either for the Pack Support row or the entire row as per your requirement.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena)

3

Hello,

Thank you for your help! Your answer was very clear.

I almost have what I want; however, I still have some questions:

  • Following your previous instructions, I have obtained the columns TotalObUnits_Week24 and Sum. However, I would like to calculate the rate for Pack Support by dividing these two calculated fields.

image

The problem is that both calculated fields are under a custom aggregation, so I can’t perform additional calculations on them:

image

Here is the error I get when I try to calculate the rate for Pack Support:

image
image760×129 17.2 KB

  • Also, this method works only when I specify the week in the calculated field, such as for week 24. How can I apply this calculation for each week? Is it possible to use a variable for the week number?

Additionally, as you mentioned in your previous message, it might be easier to create a Quicksight Arena for better visualization and troubleshooting. However, the database I am using is subject to access restrictions, so I am not sure if I can share it.

Thank you again for your assistance!

Best regards,

4

Hi @juditliz, to calculate the rate for Pack Support by dividing the two calculated fields, you will need to ensure that both fields are consistently aggregated and that the calculation is applied dynamically across different weeks. Here’s a step-by-step guide on how to address the issue:

Step 1: Create Aggregated Fields

First, ensure that your fields TotalObUnits_Week24 and Sum are aggregated correctly.

  1. TotalObUnits_Week24:
  • If it’s a sum of values for week 24, make sure it’s using the sum aggregation.
  1. Sum:
  • Similarly, ensure this is an aggregated field, such as the sum of some other metric.

Step 2: Create Dynamic Calculations

To apply the calculation for each week dynamically, you need to use parameters and calculated fields in Amazon QuickSight.

  1. Create a Parameter for Week:
  • Go to Parameters.
  • Create a new parameter, name it SelectedWeek.
  • Set the data type to Integer or String based on your week format.
  • Set the control type to Dropdown and add possible values (e.g., 24, 25, 26, …).
  1. Create a Calculated Field for TotalObUnits based on Selected Week:
  • Create a new calculated field, name it TotalObUnits_SelectedWeek.
  • Use the following formula:
sumIf({TotalObUnits}, {week_number} = ${SelectedWeek})
  1. Create a Calculated Field for Sum based on Selected Week:
  • Create another calculated field, name it Sum_SelectedWeek.
  • Use the following formula:
sumIf({Sum}, {week_number} = ${SelectedWeek})

Step 3: Calculate the Rate for Pack Support

Now, you can create a new calculated field to calculate the rate for Pack Support.

  1. Create a Calculated Field for the Rate:
  • Name it RatePackSupport.
  • Use the following formula:
{PackSupportHours} / {Sum_SelectedWeek}

Step 4: Add to Analysis

  1. Add the Parameter Control to the Analysis:
  • Go to the Parameters section.
  • Add the SelectedWeek parameter control to your analysis.
  1. Add the Calculated Fields to Your Visual:
  • Add RatePackSupport to your visual.
  • Ensure the visual updates based on the selected week from the parameter control.

This way, you can dynamically calculate the rate for Pack Support for any selected week without encountering aggregation issues.

Final Notes

  • Ensure that all fields used in calculations are correctly aggregated.
  • Adjust the parameter values according to your data (e.g., week numbers).

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved.

If you encounter any further issues, provide additional context or screenshots, and I’ll be happy to assist further.

Thanks for posting your questions on the QuickSight Community!