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

    Calculating % of dimension, then averaging

    Oli K



      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? :-)