1 Reply Latest reply on Aug 30, 2013 2:28 AM by VictorFagundo

    How to get Average of a column in UNION query


      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



      BuildingMetric 1Metric 2



      So as I show in the pivot the result is


      BuildingMetric 1Metric 2



      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 version

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

          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?