Add field Labels as x-axis values in bar chart

Question & Answer
, , ,
3

Hi @MKM, to create a bar chart in Amazon QuickSight that shows the duty cycle distribution grouped by trip type (base/auto) with calculated field labels as the x-axis values, you can follow these steps:

  1. Load Your Dataset: Make sure your dataset is loaded into Amazon QuickSight.
  2. Create Calculated Fields:
  • You need to create calculated fields for each duty cycle (0%, 10%, 20%, etc.). You can do this by adding a calculated field for each percentage. For instance, if your field names are duty_cycle_0, duty_cycle_10, and so on, you will create fields that sum these values grouped by trip type.
  1. Transform the Data:
  • Pivot your data such that you have a DutyCycle column and a Seconds column. This will allow you to use the DutyCycle as the x-axis and the sum of Seconds as the value.Here’s a step-by-step process to achieve this:

Step-by-Step Guide

Step 1: Load Your Dataset

Ensure your dataset with duty cycle fields and trip type labels is loaded into QuickSight.

Step 2: Create a Calculated Field for Trip Type

Create a calculated field to determine the trip type based on the auto miles:

ifelse({auto_miles} / {total_miles} > 0.8, 'Auto', 'Base')

Step 3: Unpivot Your Data

Since QuickSight doesn’t natively support pivoting, you’ll need to reshape your data using a data preparation tool like AWS Glue or an external tool like Python pandas. The reshaped data should have columns for TripID, DutyCycle, Seconds, and TripType.

Example in pandas, or you can use AWS Glue - ETL Service:

import pandas as pd

# Assuming df is your dataframe
df_melted = pd.melt(df, id_vars=['TripID', 'TripType'], 
                    value_vars=['duty_cycle_0', 'duty_cycle_10', 'duty_cycle_20', 
                                'duty_cycle_30', 'duty_cycle_40', 'duty_cycle_50', 
                                'duty_cycle_60', 'duty_cycle_70', 'duty_cycle_80', 
                                'duty_cycle_90', 'duty_cycle_100'],
                    var_name='DutyCycle', value_name='Seconds')

# Save this new dataframe to a CSV and load it back into QuickSight
df_melted.to_csv('transformed_data.csv', index=False)

Load this transformed_data.csv back into QuickSight.

Step 4: Create the Bar Chart in QuickSight

  1. Create a New Analysis: Start a new analysis and select your transformed dataset.
  2. Add a Visual: Choose a bar chart visual.
  3. Configure the X-Axis: Drag the DutyCycle field to the X-axis.
  4. Configure the Value: Drag the Seconds field to the Value field.
  5. Group by Trip Type: Drag the TripType field to the Color field to differentiate between Base and Auto.

Step 5: Customize the Chart

  1. Sort the Duty Cycle: Ensure the DutyCycle is sorted in the correct order (0%, 10%, 20%, etc.).
  2. Add Labels and Titles: Add appropriate labels, titles, and adjust the color scheme if necessary.

This process will give you a bar chart similar to the one in your provided image, with duty cycle distribution grouped by trip type.

I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)