SUMX SUMMARIZE not working as intended

[rjiang]

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)

[rjiang]

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

[rjiang]

Sorry use this one instead

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

[tamerj1]

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

[rjiang]

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

[tamerj1]

@rjiang 

What is on the rows of your matrix visual

[rjiang]

CompanyID and gstClientID

[tamerj1]

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

[rjiang]

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?

[tamerj1]

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

[rjiang]

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

[tamerj1]

Are these two columns in the same table?

[rjiang]

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

[tamerj1]

Ok thebn add the to SUMMARIZE 

[tamerj1]

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

[rjiang]

I tried this and now it seems to be overcounting

[tamerj1]

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

[tamerj1]

Do you have time slicer?

[tamerj1]

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

[rjiang]

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

[tamerj1]

The only thing which was missing is the Year Month column from the date table. 

Active = 
SUMX (
    ADDCOLUMNS (
        CROSSJOIN (
            SUMMARIZE (
                'Usage Data',
                'Usage Data'[gstClientID],
                'Usage Data'[gstCompanyID]
            ),
            VALUES ( 'Date Lookup'[Year Month] )
        ),
        "ClicksLast3Months", [Clicks Last 3 Months]
    ),
    IF ( [Clicks Last 3 Months] >= 3, 1, 0 )
)

However I was unable to get the numbers you've mentined above