Total of a Measure with IF

[Jamey]

The following Measure gives me a '1' in each row it's supposed to but the total still gives me the grand total of all the rows it's evaluating (RepeatCust = 207). In my current data set the RepeatCust produces 207 customers and the CustThisPeriod produces 181 in their respective columns.  The total of the matched rows should be 26, which is the difference between the two variables. What needs to change in order to give me the corect total? My issue is in my RETURN statement.

Lost Customer =

    VAR RepeatCust =

    CALCULATETABLE(VALUES(FCI_SALES[CUST_ID]),

                DATESBETWEEN(Dates[Date],

                            FIRSTDATE(Dates[Date]),

                            LASTDATE(Dates[Date])

                            )

                    )

    VAR RepeatCustAnswer = COUNTROWS(RepeatCust)

    VAR CustThisPeriod = CALCULATETABLE(VALUES(FCI_SALES[CUST_ID]),

                                DATESBETWEEN(Dates[Date],

                                            DATEADD(LASTDATE(Dates[Date]),-1*Period[Months],MONTH),

                                            LASTDATE(Dates[Date])

                                            )

                                    )

    VAR CustThisPeriodAnswer = COUNTROWS(CustThisPeriod)

RETURN IF(RepeatCustAnswer>0 && CustThisPeriodAnswer<>1,COUNTROWS(RepeatCust))

[Greg_Deckler]

@Jamey This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Jamey]

@Greg_Deckler Hi Greg, I'm still struggling with this. Below is an example:

New Customers Yes/No = IF([Repeat Customers ALL]>0 &&[Last Period Revenue]=[Total Life Time Sales]

                     ,1)

[Greg_Deckler]

@Jamey So, if New Customers Yes/No is a measure and it returns the right figure in your visual then construct the following measure and use it in your table visual instead. Keep the original measure but it doesn't need to be in the table.

New Customers Yes/No Total = 
  VAR __Table = SUMMARIZE('Table',[CUST_ID],"__Value",[New Customers Yes/No])
RETURN
  IF(HASONEVALUE('Table'[CUST_ID]), [New Customers Yes/No], SUMX(__Table,[__Value]))

[Jamey]

@Greg_Deckler I spoke too soon. The total in my case should be 9 not 64.

[Jamey]

@Greg_Deckler Thanks Greg that worked at the CUSTID level but when I added a YearMonthID the monthly totals are not correct. What needs to be added for this? Thanks again, I really appreciate you!

[Greg_Deckler]

@Jamey This is going to require sample data to recreate. Now instead of a simple measure totals problem we are now dealing with MM3TR&R. Plus your measure is using CALCULATE/CALCULATETABLE and Time Intelligence functions so it likely needs reworked as these functions all tend to create havoc. Watch my latest YouTube videos for  more on that.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[Jamey]

@Greg_Deckler I refactored my Measure to show what the other measures where doing. I have a Slicer that selects the last 1 year of dates from my data and a parameter Slicer (Period) that can select a month up to 24 months (used in the LastRevenue), the default is the last 4 months. This correctly captures which Customers are new but does not total them.

New Customers 1 =

    VAR RepeatCustAll =

    CALCULATETABLE(VALUES('Table'[CUST_ID]),

                DATESBETWEEN(Dates[Date],

                            FIRSTDATE(Dates[Date]),

                            LASTDATE(Dates[Date])

                            )

                    )

 VAR RepeatCustAnswer = COUNTROWS(RepeatCustAll)

    VAR LastRevenue =

    CALCULATE(SUM('Table'[SALES]),

               DATESBETWEEN(

                            Dates[Date],

                            DATEADD(LASTDATE(Dates[Date]),-Period[Months],MONTH),

                            LASTDATE(Dates[Date])

                            )

             )

    VAR LifeTimeSales = CALCULATE('Table'[Sales Total],ALL(Dates[Date]),VALUES('Table'[CUST_ID]))  

VAR NewCustomers = IF(RepeatCustAnswer>0 && LastRevenue = LifeTimeSales,1)

RETURN NewCustomers

[Jamey]

@Greg_Deckler Do you have a specific YouTube video? I found your latest video related to a Better Average Per Category. Are you willing to do a Teams Meeting to better understand my delima? I'm willing to pay!

[Greg_Deckler]

@Jamey - I would recommend the "Don't Use CALCULATE video but let's give this one more go. This stuff is very hard without sample data and knowing all of the relationships involved and what columns are coming from which tables because it really, really matters when you are using CALCULATE/CALCULATETABLE, etc. versus the way I do it where it tends not to matter.

New Customers Yes/No Total = 
  VAR __Table = SUMMARIZE('Table',[CUST_ID],"__Value",[New Customers Yes/No])
RETURN
  SWITCH(TRUE(),
    ISINSCOPE('Table'[YearMonthID]),SUMX(__Table,[__Value]),
    ISINSCOPE('Table'[CUST_ID],[New Customers Yes/No]),
    SUMX(__Table,[__Value])
  )

[Jamey]

Thanks for the reply, I'm still not able to get either of your suggested solutions to work. I either get no total or an extremely high total. I need a more straight forward answer for Dummy's. 🙂