4 Replies Latest reply on Aug 6, 2018 4:06 AM by Amarnath Mohan

    Custom Tables - measures in different rows

    Amarnath Mohan



      I have 9 variables A1,A2,A3,B1,B2,B3,C1,C2,C3. Each of them has either 0/1/Null. I want to create a 3 X 3 table with 9 cells with each cell displaying the % of 1s in each (Excluding the nulls) - i.e., 3 rows for A,B. For e.g, in the attached sample workbook, the sheet should have the following output:-


                A          B          C


      1      70%     33.33%    33.33%


      2      50%      40%          60%


      3      80%      60%           30%



      (The %s are number of 1s divided by number of non-nulls)


      I am happy to create 9 new calculated variables to calculate these %s  : Count (iif variable =0,1,0))/count (iif (isnull (variable),0,1)) which gives me the right %s. However I am only able to get all these in a single row. How do I display these in the format I want?




      (Added an excel sample as well.)