Countrows on calculatetable not showing total unless filter is met

[Anonymous]

I have the following piece of code to try and calculate the number of members that have lost more than 20% in turnover the last month.

VAR __PREV_MONTH = CALCULATE([Turnover Sum], DATEADD('Date'[Date], -1, MONTH))

    VAR MOM_RESULT = DIVIDE([Turnover Sum] - __PREV_MONTH, __PREV_MONTH)

VAR RESULT = CALCULATETABLE(VALUES('Purchase Joined'[Member Key]), FILTER('Purchase Joined', MOM_RESULT < -0.2))

RETURN

COUNTROWS(RESULT)

The first two variables are simply calculating the turnover and month-over-month.

Everything works fine except that it won't return the total. The reason for that is that the variable RESULT has the filter on -0.2, which is what I want to evaluate for each member. But the total Month-over-Month aggregated on all members is a positive number on 30%. 

If I change the code to the following:

CALCULATETABLE(VALUES('Purchase Joined'[Member Key]), FILTER('Purchase Joined', MOM_RESULT > -0.2))

Basically looking for any members with a Month-over-Month larger than -0.2 (-20%), it will show the total. So what happens here is that the total row is looking at the filter criteria as well and filters based on that. In my head that's incredibly stupid, but hey maybe there's a reason for that in Power BI. 

Now to my question: How can I solve this? I simply want to count the number of members with that filter. Is it possible for me to use the filter function but let the total row ignore that and simply sum everything? 

[amitchandak]

@Anonymous , Try like

last month.
VAR __PREV_MONTH = CALCULATE([Turnover Sum], DATEADD('Date'[Date], -1, MONTH))
VAR MOM_RESULT = DIVIDE([Turnover Sum] - __PREV_MONTH, __PREV_MONTH)
VAR RESULT = Countx(FILTER('Purchase Joined',VALUES('Purchase Joined'[Member Key]), MOM_RESULT < -0.2),MOM_RESULT)
RETURN
RESULT

[Anonymous]

Doesn't work. Your query does not work, but I changed it to: 

VAR RESULT = Countx(FILTER('Purchase Joined',VALUES('Purchase Joined'[Member Key])), MOM_RESULT < -0.2)

RETURN

RESULT

Received the error message:

MdxScript(Model) (163, 90) Calculation error in measure 'Purchase Joined'[Members at risk MoM]: The function COUNTX cannot work with values of type Boolean.

[amitchandak]

@Anonymous , My Mistake

VAR __PREV_MONTH = CALCULATE([Turnover Sum], DATEADD('Date'[Date], -1, MONTH))
VAR MOM_RESULT = DIVIDE([Turnover Sum] - __PREV_MONTH, __PREV_MONTH)
VAR RESULT = Countx(FILTER(VALUES('Purchase Joined'[Member Key]), MOM_RESULT < -0.2),MOM_RESULT)
RETURN
RESULT

[Anonymous]

I get the same result as previously. It won't show the totals due to the total not being less than -0.2. If I "flip" the greater than sign (MOM_RESULT > -0.2), it shows the total. That is due to the total being 28.36%, which is larger than -20%. So your query still evaluates the code on a row-by-row basis.