I’m having trouble creating a bar chart in QuickSight.
Here’s the situation:
I have one table with airport employees (with fields: Employee, Day, Month, Year, Airport, Worked Hours) and another table with airports (with fields: Airport, Month, Year, Apas). I want to calculate a KPI: Worked Hours divided by Apas.
What I’ve done is create a dataset with a join on Airport, Month, and Year. This gives me a combined dataset, but due to the different levels of aggregation, the Apa values get duplicated for each employee in the same airport and same Month and Year.
So I end up with a table like this:
My goal is to create a Bar Chart where:
- X-axis = Airports
- Y-axis = Worked Hours / Apas
For example, if I filter for two months (e.g., February and March), the calculation should be:
(Sum of all worked hours from all employees in Feb and Mar) / (4 + 5)
How can I build this measure correctly?
I’ve created the following calculated field:
sum({Hours}) / sumOver(avg(apas), [Airport, Month, Year])
This seems to work correctly when I display it in a table visual grouped by Airport, Month, and Year. However, when I switch to a bar chart and try to remove Year and Month, the visual returns an error.
Thanks in advance!
