Calculate Average House Age Across Unique House Number (Ignoring Filters)

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

Hi QuickSight Community,

I’m trying to create a calculated field in either dataset preparation or analysis view that gives me the **average HouseAge across unique House Numbers **, and I want the same result (e.g., 22) to appear on every row in my dataset or visual (Ignoring Filters).

Sample dataset:

CaseKey HouseNumber HouseAge
1 391291 24
1 391291 24
1 391291 24
2 391291 24
3 350292 20

Expected Result:

Since there are two unique House Numbers:

  • 391291 → avg = 24
  • 350292 → avg = 20
    So, (24 + 20) / 2 = 22 → This value should appear in a new field on every row, like below.
CaseKey HouseNumber HouseAge Averge HouseAge
1 391291 24 22
1 391291 24 22
1 391291 24 22
2 391291 24 22
3 350292 20 22

Would really appreciate any guidance or best practices on how to achieve this.
@duncan @Giridhar.Prabhu @ytakahr

Thanks in advance!

2

Hi @Purushothaman

A similar example that I have used is below. I want to get the Max of a datetime value from across my dataset and it can be done as follows. You will need to use the avg and avgOver instead.

You can do it in the Analysis.

image

image
image634×253 20.3 KB

1 Like
3

Hi @Giridhar.Prabhu ,

Thank you for suggestion, I tried with avgover Avg but still getting that particular House age number based on the House Number selection.

image
image276×132 1.72 KB

image
image458×146 6.18 KB

Expected outcome is 22. Based on the selection postalcode, the unique postalcode House age is (24+20) /2 = 22.

Another approach would you recommend?

Thank you!

4

Hi @Purushothaman,
It’s been awhile since we last heard from you on this thread, are you still working on this case or were you able to find a work around in the interim?

Are you filtering out other potential postal code options? What if you added in ‘PRE_FILTER’ to your calc. field; does that change the outcome?

Let us know if you were able to find an alternate work around for this case; if we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

5

Hi @Purushothaman,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!