This discussion is archived

How to get Average of a column in UNION query

Currently Being Moderated

Hi All,

I will try to explain the issue as much as I can and If you need clarification on any piece please let me know.

So, I have a Union Query with two sets of Criteria.

The first Criteria Brings the list of Buildings and a set of Measures for it. The second criteria does the same but with a different set of filters.

No I use a UNION between these both criteria and present them in a Pivot view so that we only have one row per each building. The values in each of the criteria in the UNION query are Summed

Ex:

BuildingMetric 1Metric 2
12030
12535
24050
24555

So as I show in the pivot the result is

BuildingMetric 1Metric 2
14565
285105

Now the issue is with the Grand Total.

I want the Grand Total to be an Average of the Buildings. So for Metric 1 The Grand Total should be (45+85)/2 because we have two buildings so the answer should be 65.

How can I get that.

Bottom Line: Need an Average as the Grand Total when the Aggregation rule on the column is set to Sum.

we use 11.1.6.10 version

• 1. Re: How to get Average of a column in UNION query
Currently Being Moderated

Hi VJ

What you are asking is impossible in the pivot table itself as you are asking one part of the pivot table to aggregate via one method, and another to aggregate via another method, on the same column ..

It can be done using a calculated item. For your example above you'd use the formula ( \$1 + \$2) / 2.

.. But thats HIGHLY explicit, and would only work if you KNOW how many buildings will come back in the query.. EVERYTIME.

Why not restructure the query to combine it into a single query using an combinations of filters with OR in between?

Legend

• Correct Answers - 10 points