Answered by:
Summing measure with constant and ALL() returns incorrect results

-
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!
Question
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
- Edited by Laurence Cross Tuesday, April 29, 2014 9:21 AM
- Proposed as answer by Michael AmadiModerator Tuesday, April 29, 2014 11:11 AM
- Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, May 07, 2014 2:20 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- Edited by Michael AmadiModerator Tuesday, April 29, 2014 11:11 AM
- Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, May 07, 2014 2:20 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
- Edited by Laurence Cross Tuesday, April 29, 2014 9:21 AM
- Proposed as answer by Michael AmadiModerator Tuesday, April 29, 2014 11:11 AM
- Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, May 07, 2014 2:20 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- Edited by Michael AmadiModerator Tuesday, April 29, 2014 11:11 AM
- Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, May 07, 2014 2:20 AM