topic Re: Total of a Measure with IF in DAX Commands and Tips

Total of a Measure with IF

Jamey — Wed, 14 Sep 2022 20:24:10 GMT

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

Re: Total of a Measure with IF

Greg_Deckler — Wed, 14 Sep 2022 20:53:32 GMT

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

Re: Total of a Measure with IF

Jamey — Thu, 15 Sep 2022 12:59:38 GMT

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. 🙂

Re: Total of a Measure with IF

Jamey — Mon, 19 Sep 2022 16:23:37 GMT

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)

Re: Total of a Measure with IF

Greg_Deckler — Mon, 19 Sep 2022 16:35:04 GMT

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

Re: Total of a Measure with IF

Jamey — Mon, 19 Sep 2022 16:49:57 GMT

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!

Re: Total of a Measure with IF

Jamey — Mon, 19 Sep 2022 17:24:04 GMT

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

Re: Total of a Measure with IF

Greg_Deckler — Mon, 19 Sep 2022 18:04:23 GMT

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-Roll/m-p/411443#M150

Re: Total of a Measure with IF

Jamey — Mon, 19 Sep 2022 18:16:51 GMT

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!

Re: Total of a Measure with IF

Jamey — Mon, 19 Sep 2022 19:03:18 GMT

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

Re: Total of a Measure with IF

Greg_Deckler — Mon, 19 Sep 2022 19:37:14 GMT

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