2 Replies Latest reply on Nov 25, 2016 11:41 AM by Eric Hedekar

    Comparing rank of average across two demographics

    Eric Hedekar

      I have a set of average weighted ratings for each singer.  This has been split into two sheets, one showing female respondents, one showing male respondents, and each sheet has then been ranked.  I'd love to create a difference score for each group but I have no idea how to get this going.  By difference score I mean if males on average rate Celine Dion a 4.3/10 and females rate her a 9.2/10 then her difference score should be +4.9 (or -4.9, either direction works).  Sadly I'm working with client data so I can't share a workbook.


      I'd also love to get a difference rank.  As in if males rank Celine Dion the 30th best singer in the set (based on the weighted average score) and females rate her as the 2nd best then her difference rank would be +28 (or -28).


      The weighted average is a calculated field.  The Male/Female sheets are built the same but with filters in place on the gender variable.


      I tried building a calculation that used IF([Gender]="Male") THEN ([Weighted Average]) but because the weighted average is an aggregate function this didn't work.  Do I need to rebuild my average calculation with every variable wrapped in an IF([Gender]="Male") case logic?