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

    How to get Average of a column in UNION query

    vjbez1

      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
          VictorFagundo

          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?