cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Not the usual totals calculation issue

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Not the usual totals calculation problem

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]))

View solution in original post

3 REPLIES 3
Highlighted
Solution Sage
Solution Sage

Re: Not the usual totals calculation problem

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!

Highlighted
Frequent Visitor

Re: Not the usual totals calculation problem

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.

Highlighted
Frequent Visitor

Re: Not the usual totals calculation problem

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]))

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors