Not the usual totals calculation issue

[grechill]

Hello all,

I have an issue with getting the totals right. 

I'm trying to do month on month comparison to define if customer has stopped paying on the contract or we have gained the customer (churn rate). There are multiple product types and I would like to treat them separate. 

Previous month value:

Amount PM = CALCULATE([Amount],DATEADD('Calendar'[Date],-1,MONTH))

Intermediate formula to define if its a lost or new customer and to be summed:

z_int_Lost Customer Flag = 
IF(
    AND(
        [Amount]<=0,[Amount PM]>0),
        1,0)

z_int_New Customer Flag = 
IF(
    AND(
        [Amount]>0,[Amount PM]=0),
        1,0) 

Here is my usual totals fix, however it doesn't work this time

Test 1 = 
IF(
    HASONEFILTER(Data[Customer]),
    [z_int_Lost Customer Flag],
    SUMX(VALUES(Data[Customer]),[z_int_Lost Customer Flag]))

Also I have tried 2 other solutions found online, but they do not work either.

Test 2 = 
VAR _total = SUMMARIZE(Data,Data[Customer],'Calendar'[Year Month],'Product'[Product Group],"Lost",[z_int_Lost Customer Flag])
RETURN
IF(
    HASONEFILTER(Data[Customer]),
    [z_int_Lost Customer Flag],
    SUMX(_total,[Lost]))

Test 3 = SUMX(SUMMARIZE(Data,Data[Customer],'Calendar'[Year Month],'Product'[Product Group],"Lost",[z_int_Lost Customer Flag]),[Lost])

Finally, I have done a separate table with bridge connectors, as is described in this post. However, this solution is not flexible and I would like to avoid it.

I attach a mockup PBIX with replicated totals issue. Its has identical structure and measures. 

UPD: Added more data to the set and proposed option.

Looking forward for your help!

Kind regards,

Sergey

[richbenmintz]

Hello @grechill,

See attachment, smaple.pbix

I created a customer dimension and then used the following measure

Lost Customers = CALCULATE(COUNTX('Customers', COUNTROWS('Customers')), filter('Customers', [Amount]<=0 && [Amount PM] > 0))

I hope this helps,
Richard
Have I answered your question? Mark my position as a solution!
Did my answers help come up with a solution? Give it a kudos by clicking Thumbs Up!

[grechill · Accepted solution]

Ok, I have figured it out.

I have used proposed solution by @richbenmintz and added a standard tweak to fix subtotals and totals for year/quarter/month.

Lost customer =

IF(

HASONEFILTER(dim_Customer[Customer]),

[z_Lost Customer],

SUMX(VALUES('Calendar'[Year Month]),[z_Lost Customer]))

[grechill]

Hi @richbenmintz ,

Sorry, I was too quick with assessment. It doesn't work. It shows subtotals, but they are incorrect. And it doesn't show totals at all.

I have updated data set with some data to test it and added your solution.