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

    Custom Tables - measures in different rows

    Amarnath Mohan

      Hi,

       

      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?

       

      Thanks.

       

      (Added an excel sample as well.)