Forum Discussion
Subtotal isn't adding up correctly
Hi all,
We are looking for a measure that finds new customers per month. To do this we are looking at the sales 12 months prior. Whenever a customer has no sales for 12 months and has sales the 13th month it is then identified as a new customer in the 13th month. Row by row the new customers add up, since we validated them in Excel, however when we add the subtotal, it doesn't add up (see picture below):
We used these measures:
SalesTest = SUM('Sales Shipped'[_Shipped Value])
PriorSalesTest =
CALCULATE(
SUM('Sales Shipped'[_Shipped Value]),
FILTER(
ALL('Calendar Date'),
'Calendar Date'[Calendar YearMonthNumber] >= SELECTEDVALUE('Calendar Date'[Calendar YearMonthNumber]) - 11
&& 'Calendar Date'[Calendar YearMonthNumber] <= SELECTEDVALUE('Calendar Date'[Calendar YearMonthNumber]) - 1
)
)
NewCustomerSales_Monthly =
CALCULATE(
SUM('Sales Shipped'[_Shipped Value]),
FILTER(
VALUES('Customer Order Point'[Customer Number]),
[SalesTest] > 0 && [PriorSalesTest] = 0
)
)
NewCustomerCount_Monthly =
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Customer Order Point'[Customer Number]),
[SalesTest] > 0 && [PriorSalesTest] = 0
)
)
)
I have excluded the shipped value column in the picture as it contains sensitive information, but the same happens as the the #new customers column. Row by row it is the correct value, but subtotally it is not.
I hope someone can help us find what we did wrong here.
Thanks!
Hi Anonymous It might be because the measure NewCustomerCount_Monthly evaluates subtotals in the overall context instead of summing individual rows, leading to incorrect aggregation. To fix this, use SUMX to explicitly iterate and sum the row-level results.
5 Replies
Hi Anonymous It might be because the measure NewCustomerCount_Monthly evaluates subtotals in the overall context instead of summing individual rows, leading to incorrect aggregation. To fix this, use SUMX to explicitly iterate and sum the row-level results.
Hi Anonymous ,
we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @Muratmet123 ,
we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @Muratmet123 ,
we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
