SUMX card keeps returning 0

[Ellis_Woods]

Hi all, i hope you're well!!

I am really struggling with why my SUMX function keeps returning zero. I am attempting to do a customer churn dashboard with revenue column (easy), last period sales: 

Last Period's Sales = CALCULATE(

    SUM(Table_Query_from_MMS[Revenue]),

    DATESINPERIOD(

        Table_Query_from_MMS[DocumentDate].[Date],

        LASTDATE(Table_Query_from_MMS[DocumentDate].[Date]),

        -12,

        MONTH)

)

, revenue 2022 (again straight forward), and then lost customers:

Lost Customers 2023 = if(AND([Last Period's Sales]=0,[Revenue 2022]>0),1,0).

From this i just want to find out the overall count of lost customers to put it as a card. When doing this, it just returns 0.

When you scroll down on this, 1's appear for the customers that ordered in 2022 but not 2023. it should =85 but does not.

Id really appreciate some advice as im quite new and its really doing my head in!

[ValtteriN]

Hi,

could you provide the dax for the SUMX in your card measure:

Here is a working example based on a different condition as a reference:

Data:

Dax:

test = IF(MIN('Table (19)'[Column1])=0,0,1)

Measure 11 = SUMX('Table (19)',[test])

[Ellis_Woods]

This is the DAX for the card:

Lost customers 2023 count = SUMX(Table_Query_from_MMS,Table_Query_from_MMS[Lost Customers 2023])

[ValtteriN]

Hi,

It is a bit tricky to confirm is guess is correct, but my assumption is the following:

I think the table visual has a different filter context than your fact table (Table_Query_from_MMS). Because of this when the [Lost Customers 2023] is evaluated over the table the rows are all 0.

Here is an example of this behaviour with my demo data:

Here the table shows 2 rows with 1's. However the card shows 3. This is because when the table contains 3 rows with dim b:

Now when the SUMX evaluates the table without filters -> as per the interaction in card the result is 3.

In your case when if(AND([Last Period's Sales]=0,[Revenue 2022]>0),1,0) is evaluated row by row over Table_Query_from_MM each row likely gets the value 0.

[Ellis_Woods]

Thank you so much for getting back to me! I'm really stumpped!

My Lost customer column is not just returning 0. All together out of this long list, 85 rows return the value of 1. The only issue is summing these up and displaying it on a card.

[ValtteriN]

Hi,

What I meant was that since SUMX will evaluate the formula over your table as seen in table view:

And not as shown in your visual the result might be different eventhough your visual shows 1's.

Basically consider how the IF condition evaluates if it was a added as a calculated column.