Forum Discussion
Sub totals not summarizing correctly
Hello,
I downloaded "New and Returning Customers" from the quick measures gallery.
It is a great example...but I do not understand why the yearly subtotals do not sum correctly. If you click on the link above....you will be able to see the matrix (the example is available for download as well)....
For example: Year 2007
The martix shows the following Sums for 2007
Customers | New Customers | Returning Customers
-1409 -1409 -0
It seems like the Sums should equal the following
Customers | New Customers | Returning Customers
-1498 -1409 -89
I appreciate any help...with understanding why it is summarizing like it is....as well as --- How to get the sums to total correctly.
Thanks
It depends on how you define a returning customer.
If it is the difference between customers and new customers, 0 is the right answer at the year level.
A new customer in January that returns in April, is still a new customer when you look at the year level.
If you want to implement a different definition, you have to change the calculation logic.
Marco Russo - SQLBI
3 Replies
belmore wrote:
Hello,
I downloaded "New and Returning Customers" from the quick measures gallery.
It is a great example...but I do not understand why the yearly subtotals do not sum correctly. If you click on the link above....you will be able to see the matrix (the example is available for download as well)....
For example: Year 2007
The martix shows the following Sums for 2007
Customers | New Customers | Returning Customers
-1409 -1409 -0
It seems like the Sums should equal the following
Customers | New Customers | Returning Customers
-1498 -1409 -89
I appreciate any help...with understanding why it is summarizing like it is....as well as --- How to get the sums to total correctly.
Thanks
May I know how do you address the 1498? When I test in a table visual, the Customers shows 1409.
By the way, for any further questions or confusions about those measures, I think marcorusso can help.
It depends on how you define a returning customer.
If it is the difference between customers and new customers, 0 is the right answer at the year level.
A new customer in January that returns in April, is still a new customer when you look at the year level.
If you want to implement a different definition, you have to change the calculation logic.
Marco Russo - SQLBI
Marco,
Thank you for taking time to reply. I understand know....your calculation for the Customers column is identifying Unique Customer's for each month and the yearly/annual sub-totals equal Unique Customers for that Year. I was adding up annual customers by totaling unique customers per month -- which is not correct - Thanks for your Help!!!!
