topic Re: SUMX SUMMARIZE not working as intended in DAX Commands and Tips

SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 16:59:46 GMT

I am trying to get a matrix to display the results of this measure. Essentially, I want it to flag a user with a 1 if the clicks in the last 3 months are greater than or equal to 3 and flag with a 0 otherwise. However, the measure shown below is not evaulating as intended.

Active =

VAR Active =

SUMX(

SUMMARIZE('Usage Data','Usage Data'[gstClientID]),

IF([Clicks Last 3 Months]>=3,1,0)

)

RETURN IF(ISBLANK(ACTIVE),0,ACTIVE)

Here is the measure (working as expected) used in [Active]

Clicks Last 3 Months =

VAR Clicks_Last_3 =

CALCULATE(

SUMX(VALUES('Usage Data'[gstClientID]),[Clicks]),

DATESINPERIOD('Date Lookup'[Date],

DATE(

YEAR(MAX('Date Lookup'[Date])),

MONTH(MAX('Date Lookup'[Date])),1)-1,

-3,

MONTH

)

RETURN IF(ISBLANK(Clicks_Last_3),0,Clicks_Last_3)

//Calculates clicks in the last 3 months (does not include current month)

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 18:00:23 GMT

Hi rjiang

You can try

VAR Active = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Usage Data', 'Usage Data'[gstClientID] ), "@ClicksLast3Month", [Clicks Last 3 Months] ), IF ( [@ClicksLast3Month] >= 3, 1, 0 ) )

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 18:36:23 GMT

Thanks. The output has changed slightly, but I'm still significantly off from what's expected

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 18:37:42 GMT

rjiang

What is on the rows of your matrix visual

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 18:39:29 GMT

CompanyID and gstClientID

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 18:48:31 GMT

rjiang

Then try this

VAR Active = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Usage Data', 'Usage Data'[CompanyID], 'Usage Data'[gstClientID] ), "@ClicksLast3Month", [Clicks Last 3 Months] ), IF ( [@ClicksLast3Month] >= 3, 1, 0 )

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 19:03:39 GMT

Closer, but still not what I'd expect.

I'm noticing that the more fields I add to the summarize function, the higher the counts go. How do I know which fields should be included or excluded?

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:07:30 GMT

Do you have anything else on the slicers? Maybe from other tables?

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 19:09:27 GMT

Yes, there are filters on all pages for CompanyName and ProductName

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:11:38 GMT

Are these two columns in the same table?

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 19:16:03 GMT

Yes they are columns in the table, but not in the matrix

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:20:07 GMT

Ok thebn add the to SUMMARIZE

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:28:22 GMT

Active = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Usage Data', 'Usage Data'[CompanyID], 'Usage Data'[gstClientID], 'Usage Data'[CompanyName], 'Usage Data'[ProductName] ), "@ClicksLast3Month", [Clicks Last 3 Months] ), IF ( [@ClicksLast3Month] >= 3, 1, 0 ) )

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 19:38:02 GMT

I tried this and now it seems to be overcounting

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:40:48 GMT

Please send sample file. I'll to look into it

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:50:02 GMT

Do you have time slicer?

Re: SUMX SUMMARIZE not working as intended

tamerj1 — Fri, 01 Apr 2022 19:55:04 GMT

Tou csn also try with CALCULATE

Activew = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Usage Data', 'Usage Data'[CompanyID], 'Usage Data'[gstClientID] ), "@ClicksLast3Month", [Clicks Last 3 Months] ), CALCULATE ( IF ( [@ClicksLast3Month] >= 3, 1, 0 ) ) )

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 20:54:06 GMT

I've uploaded the sample data here:

https://drive.google.com/file/d/1S5mw2j6yK11k18JuUA0tnWVGqniNGYCm/view?usp=sharing

I am expecting 2022-03 in the Active tab to evaluate to a total of 15,825

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 21:04:57 GMT

Sorry use this one instead

https://drive.google.com/file/d/1gnQj9E1XgFEZp1zZyS7USlPn88S6vRLv/view?usp=sharing

Re: SUMX SUMMARIZE not working as intended

rjiang — Fri, 01 Apr 2022 21:13:01 GMT

I am expecting 2022-03 in the Active tab to evaluate to a total of 15,825

https://drive.google.com/file/d/1gnQj9E1XgFEZp1zZyS7USlPn88S6vRLv/view?usp=sharing