locked
Error using the summarize function RRS feed

  • 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.
    Friday, April 25, 2014 5:26 PM
    Answerer

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 summarize-function 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.
    Friday, April 25, 2014 5:26 PM
    Answerer