Answered by:
Error using the summarize function
Question

Hi,
I have the following formula, which is trying to sum the column KCU_FAIL and group by the Column HIT_CDD.
=SUMMARIZE(mttp,[HIT_CDD],"HitKCU",sum([KCU_FAIL]))
I am getting an error message saying that my expression refers to multiple columns, but can't see how that is the case. Any help would be appreciated.
James
In sql this would be
select sum(KCU_FAIL), hit_CDD
from 'mttp'
group by hit_CDD
Friday, April 25, 2014 11:30 AM
Answers

I'm a little unclear on your description of the percent but I think this might be the measure you want, however it doesn't match up with the numbers in your outcome example (are they the actual expected values?):
=COUNTROWS(mttp)/ CALCULATE( COUNTROWS(mttp), ALLEXCEPT( mttp, mttp[Col_1] ) )
If this isn't it, please clarify the explanation of the percentage you are trying to calculate. Proposed as answer by Michael Amadi Friday, April 25, 2014 10:31 PM
 Marked as answer by Olaf HelperMVP Sunday, May 4, 2014 8:14 AM
Friday, April 25, 2014 5:26 PMAnswerer
All replies

Hi Jadm,
The problem here is that summarize returns a table whereas a measure (and calculated column) needs to return a single value. That is why the summarizefunction is normally used inside another function such as AVERAGEX.
Could you explain how that measure is supposed to work and to be used?
Regards,
Julian
Julian Wissel  BI for NAV @ http://en.navbi.com  Blog @ blog.navida.eu
Friday, April 25, 2014 12:22 PM 
Hi Julian,
Thanks for your repsonse. I am trying to produce a pivot table which will show me the sum of a field (KCU_Fail) grouped by another field (Hit_CDD)
I have data that is organised like this, along with some other columns
Hit CDD KCU_fail Col_1
Y N A
Y N A
Y Y A
Y N B
Y Y B
Y N A
Y N A
Y Y B
Y N B
Y Y A
Y N A
Y Y B
N N B
N Y A
N Y A
N N C
N Y C
N N C
The output that I am after is this
Hit CDD Y N
KCU Fail Y N Y N
Col_1
A 26% 74% 34% 66%
B 51% 49% 10% 90%
B 26% 74% 31% 69%
In each cell I show the % of records that have a KCU_fail value for a given hit CDD value. Using the show values as % of row total, it’s easy to get the % of records in each combination of the two, but I want it at the HitCDD level. I could do this but putting Hit CDD into the row labels and using the % of records function, but I want it to be in the format above as it’s easy to compare.
James
Friday, April 25, 2014 1:30 PM 
I'm a little unclear on your description of the percent but I think this might be the measure you want, however it doesn't match up with the numbers in your outcome example (are they the actual expected values?):
=COUNTROWS(mttp)/ CALCULATE( COUNTROWS(mttp), ALLEXCEPT( mttp, mttp[Col_1] ) )
If this isn't it, please clarify the explanation of the percentage you are trying to calculate. Proposed as answer by Michael Amadi Friday, April 25, 2014 10:31 PM
 Marked as answer by Olaf HelperMVP Sunday, May 4, 2014 8:14 AM
Friday, April 25, 2014 5:26 PMAnswerer