2 Replies Latest reply on Sep 12, 2016 7:08 AM by Frank Casella

    Counts within a grid that uses calculated measures as the axes

    Frank Casella

      Hi, and thanks for taking the time to read.

       

      I'm trying to create a 5x5 matrix using two calculated percentages (% correct and % on time). the percentages are calculated as follows:

      • % correct: SUM(IF [Errors]=0 THEN [Widgets] ELSE 0 END)/SUM([Widgets])
      • % on time: SUM(IF DATEDIFF('day',[Due Date],[Submit Date])<1 THEN [Widgets] ELSE 0 END)/SUM([Widgets])

      I've created two additional calculations as aggregate criteria for the calculated percentages.  Both calculations are identical with the exception of which calculated measure they are splitting:

      • correct split: IF [% Correct]<=0.2 THEN '0-20%' ELSEIF [% Correct] <=0.4 THEN '21-40%' ELSEIF [% Correct]<=0.6 THEN '41-60%' ELSEIF [% Correct]<=0.8 THEN '61-80%' ELSE '81-100%' END

      I'd like the end result to be a 5x5 matrix with the count of workers that fall into each box. something like this...

      0-20%21-40%41-60%61-80%81-100%
      0-20%0141227
      21-40%2514933
      41-60%611223657
      61-80%92388196225
      81-100%1441106358982

      I've tried a bunch of different things, but apparently not the right thing quite yet.  Because the axes aren't dimensions, I can't figure out how to get the table calculations to work.

       

      Any suggestions would be greatly appreciated.

       

      Best regards,

       

      Frank