Calculated Field for Average HouseAge per Unique Postal Code by Division

Question & Answer
, , , , , , , , ,
1

Hi QuickSight Community,

I’m trying to create a calculated field in Amazon QuickSight that calculates the average of HouseAge across unique Postalcode values, grouped by Division. I’m running into some trouble and would appreciate any guidance.

Here’s a sample of my dataset:

`CaseKey Postalcode HouseAge Division
1 650291 24 AG
2 650291 24 AG
3 650291 24 AG
4 650292 20 AG
5 650293 30 PD
6 650293 30 PD
7 650294 35 PD
8 650295 (null) PD
9 650296 (null) PD`

Expected Results per Division:

  • AG Division:
    • Unique Postal Codes and their average HouseAge:
      • 650291: 24
      • 650292: 20
    • Average of unique postal code averages: (24 + 20) / 2 = 22
  • PD Division:
    • Unique Postal Codes and their average HouseAge:
      • 650293: 30
      • 650294: 35
    • (Postal codes 650295 and 650296 are ignored due to null HouseAge)
    • Average of unique postal code averages: (30 + 35) / 2 = 32.5

Expected Result:

CaseKey Postalcode HouseAge Division AverageDivisionHouseAge
1 650291 24 AG 22
1 650291 24 AG 22
1 650291 24 AG 22
2 650291 24 AG 22
3 650292 20 AG 22
4 650293 30 PD 32.5
4 650293 30 PD 32.5
5 650293 30 PD 32.5
6 650294 35 PD 32.5
7 650295 PD 32.5
8 650296 PD 32.5

I’ve been trying to use a combination of avgOver and potentially some partitioning, but I’m struggling to get the correct level of aggregation.

Could anyone provide guidance on how to create this calculated field in QuickSight? Any help or suggestions would be greatly appreciated!

@duncan @ytakahr @Giridhar.Prabhu @Rahul_Easwar

Thanks in advance,

2

Hello @Purushothaman

Just to check off the box, did avgOver({HouseAge}, [{Division}], PRE_AGG) (or any variants) not work because of an error or because it returned the wrong values?

You may need to nest your LAC function.

3

HI @duncan,

Thanks for following up.

I’ve used the following calculated field:

plaintext

CopyEdit

avg(HouseAge, [Postalcode, Division])

This gives the correct average value per division, and when I apply a Division filter (e.g., AG), the KPI card correctly shows 22 as expected.

However, the challenge is that in the actual dashboard, the filter applied to the sheet is only on Postalcode, not Division.

:white_check_mark: What I need:

  • I want the KPI card to always show the Division-level average HouseAge, like 22 for AG — even when a Postalcode filter is applied.
  • The idea is: if someone selects Postalcode 650291, the KPI should still show 22 (AG’s average) — not a recalculated value for that specific postal code.

Thank you!

1 Like
4

Hey @Purushothaman

Could you try making a duplicate of your calculation and title it with something specific for your KPI visual. In your new calculation add “PRE_FILTER” to the end, and apply it to your KPI.

Let me know if that works.

5

Hey @Purushothaman

Were you able to try my suggestion above?

6

Hi @duncan,

Thank you for your follow-up, I managed to fix by at SQL-Server Table level, due to complexity.

Should I delete my original Post?

Please suggest.

Thank you!

1 Like
7

Hello @Purushothaman

No problem, thank you for following up with how you were able to solve this!

I marked your comment as the solution, no need to delete the post.