cancel
Showing results for
Did you mean:
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]))
3 REPLIES 3
Highlighted
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

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

Announcements

#### Get Ready for Power BI Dev Camp

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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