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

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

• ###### 1. Re: Counts within a grid that uses calculated measures as the axes

Try changing both your split calculated fields to something like

{FIXED [Worker ID] : 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}

Then drag them to rows and columns, then right-click and drag Worker ID (or whatever other field you have identifying each worker) to text and select CNTD(Worker ID).

1 of 1 people found this helpful
• ###### 2. Re: Counts within a grid that uses calculated measures as the axes

Thanks Benjamin, this did the trick!