Creating a Bar Chart with Mixed Filter Sensitivity on Dual Measures in QuickSight

Question & Answer
, , , , , , ,
1

Hello QuickSight Community,

I’m transitioning a project from Power BI to AWS QuickSight and am looking for some guidance on how to replicate a specific visualization that uses two different measures on the Y-axis of a bar chart, with different filtering behaviors for each measure.

Requirements:

  1. Bar Chart with Year on the X-axis
    I need to set up a bar chart where the X-axis represents different years.
  2. Two Measures on the Y-axis:
  • First Measure: Average Population Across All Countries by Year
    This measure should calculate the average population for each year across all countries, and importantly, it should not be affected by any filters on the country.
  • Second Measure: Population of a Specific Country
    This measure should reflect the population of a specific country and should respond to country filters.

Expecting Visulaztion like this:

image
image863×554 20.9 KB

Below is the link for dataset and PBIX File:

CountryPopulation.pbix

CountryDataSet.xlsx

Current Measure used in Power BI:

  1. Average Population All Countries (ignores country filter):
    CALCULATE(
    AVERAGE(‘Table’[Population]),
    ALL(‘Table’[Country])
    )

  2. Population of Country (respects country filter):

SUM(‘Table’[Population])

Anyone, Please help.

Thank you!

2

Hi @Purushothaman ,

You will need to use Level Aware calculation to do similar calculation in QS.

You can read more about level aware calculation here :

Check out the LAC-W - AverageOver if you are looking to fetch Average of all countries irrespective of the filter. The calculated field may need changes based on your data granularity!

Alternately, you can create a sample analysis in Arena for us to collaborate and share solution.

Thanks,
Prantika

3

Hi @Purushothaman,
It’s been awhile since we last heard from you, did you have any additional questions regarding your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!