Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
belmore
Helper II
Helper II

Sub totals not summarizing correctly

 

Hello,

 

I downloaded "New and Returning Customers" from the quick measures gallery. 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297/highl...

 

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

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee


@belmore wrote:

 

Hello,

 

I downloaded "New and Returning Customers" from the quick measures gallery. 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297/highl...

 

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


@belmore

May I know how do you address the 1498? When I test in a table visual, the Customers shows 1409.

Capture.PNG

 

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!!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.