Forum Discussion
Matrix row totals not working properly
Hi, I have a matrix with year, month (both from a calendar table), and customer as row headers. As for values I have the following measure:
Lost Customers Amount =
VAR currentCustomers = VALUES(SALES[Customer])
VAR previousMonthDates = PREVIOUSMONTH('DATE'[Date])
VAR previousMonthCustomers = CALCULATETABLE(SALES[Customer]),ALL('DATE'[Date]), previousMonthDates)
VAR LostCustomers= EXCEPT(previousMonthCustomers,currentCustomers)
VAR LastMonthAmount = CALCULATE(SUM(SALES[Sales]), DATEADD('DATE'[Date], -1, MONTH), LostCustomers)
RETURN
SUMX(LostCustomers, LastMonthAmount)
The measure returns the sales of the customers who purchased the previous month but did not purchase the selected month.
The measure works fine in the matrix at customer level, but fails to aggregate the sum properly at month level.
Any idea would be very appreciated!
The measure returns the sales of the customers who purchased the previous month but did not purchase the selected month.
The measure works fine in the matrix at customer level, but fails to aggregate the sum properly at month level.
Any idea would be very appreciated!
2 Replies
- amitchandak
Super User
quikin5 ,
In such case create a new measure ,
Sumx(Values(VALUES(SALES[Customer]), [Lost Customers Amount])
Also check my approach of just using isblank and avoid expcept
Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458It didn't work 😞
