none
Summing measure with constant and ALL() returns incorrect results

    Question

  • I'm really desperate for some help, the results of my query make no sense at all!

    I am adding a 1 to a simple SUM base measure and then doing a SUMX over ALL values in a column

    So I have [Sum Users]. I add 1 to this in [Sum users plus 1]. Then for both measures I do a SUMX over ALL values in 'Dates' (which is actually ga_customVarValue4). But [Sum Users plus 1 over All Dates] returns incorrect results and for the life of me I can't understand why. The sum of values in [Sum Users] should equal [Sum Users plus 1 over All Dates] but it doesn't.

    It's definitely to do with the +1. If I replace this in the formula with a multiplier (say *2) then the results are correct.

    Any ideas? I'm really at my wits end here!

    Monday, April 28, 2014 11:14 PM

Answers

  • Hi Joanna,

    I think your problem lies in that you did the sum([ga users] +1) as a calculated field rather than creating a new column that does the calculation.

    The behaviour of doing it as a calculated field will do a sum of all the users and then add 1, rather than just adding 1 to each individual row.  When you drag the dates in, it is performing that across all the dates (adding 1 per row for each date).

    Create a new column called [GA Users plus 1] with the calculation being =[GA Users] +1 and then create your 'Sum All' calculated field at the bottom referencing [GA Users plus 1] and you should get the result you need.

    Regards


    Tuesday, April 29, 2014 9:21 AM
  • Hi Joanna,

    I think your problem lies in that you did the sum([ga users] +1) as a calculated field rather than creating a new column that does the calculation.

    The behaviour of doing it as a calculated field will do a sum of all the users and then add 1, rather than just adding 1 to each individual row.  When you drag the dates in, it is performing that across all the dates (adding 1 per row for each date).

    Create a new column called [GA Users plus 1] with the calculation being =[GA Users] +1 and then create your 'Sum All' calculated field at the bottom referencing [GA Users plus 1] and you should get the result you need.

    Regards


    Assuming that what Laurence has proposed is what you're after, you could bypass creating a calculated column by doing something along these lines...

    Column Plus 1 Then SUM:=
      SUMX(
        TableName, 
        TableName[ColumnToBeSummed] + 1
    )

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Tuesday, April 29, 2014 11:11 AM

All replies

  • Hi Joanna,

    I think your problem lies in that you did the sum([ga users] +1) as a calculated field rather than creating a new column that does the calculation.

    The behaviour of doing it as a calculated field will do a sum of all the users and then add 1, rather than just adding 1 to each individual row.  When you drag the dates in, it is performing that across all the dates (adding 1 per row for each date).

    Create a new column called [GA Users plus 1] with the calculation being =[GA Users] +1 and then create your 'Sum All' calculated field at the bottom referencing [GA Users plus 1] and you should get the result you need.

    Regards


    Tuesday, April 29, 2014 9:21 AM
  • Hi Joanna,

    I think your problem lies in that you did the sum([ga users] +1) as a calculated field rather than creating a new column that does the calculation.

    The behaviour of doing it as a calculated field will do a sum of all the users and then add 1, rather than just adding 1 to each individual row.  When you drag the dates in, it is performing that across all the dates (adding 1 per row for each date).

    Create a new column called [GA Users plus 1] with the calculation being =[GA Users] +1 and then create your 'Sum All' calculated field at the bottom referencing [GA Users plus 1] and you should get the result you need.

    Regards


    Assuming that what Laurence has proposed is what you're after, you could bypass creating a calculated column by doing something along these lines...

    Column Plus 1 Then SUM:=
      SUMX(
        TableName, 
        TableName[ColumnToBeSummed] + 1
    )

    Regards,

    Michael

    Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

    Website: nimblelearn.com, Blog: nimblelearn.com/blog, Twitter: @nimblelearn

    Tuesday, April 29, 2014 11:11 AM