10 Replies Latest reply on Jun 29, 2016 4:05 AM by Anoop Odedra

    Calculating % of dimension, then averaging

    Oli K

      Hi,

       

      Sorry I really can't share a Workbook, but I hope you can help anyway...

       

      My data has PersonID, CompanyID, Gender, Year, Sector.

       

      I am trying to:

      • Calculate the % of females for an individual company
      • Average the female percentages over all companies in a sector
      • Plot the average female percentage over the years, with one line per sector

       

      I got close by using WINDOW_AVG(sum(iif([Gender]='F',1,0))/count([Gender])), computed using Company ID.

       

      However this only works if Company ID is also in the view, and it duplicates the sector % for every company in that sector.

       

      Essentially all I need now is get the Company ID out of my view, but keep the pre-aggregation on a Company ID level.

       

      Does this make sense to anyone? :-)