Row level total in matrix incorrect for a measure

[Anonymous]

Hello, 

I have created this measure for churn:

Churn Measure =

  VAR __Churn = [Value in GBP on last day of Month (2 Months ago) V1] - [Value in GBP on last day of Month (Last Month) V1]

RETURN

  IF(__Churn > 2500,__Churn,0)

But when putting in a matrix the total is indicating 0 which is incorrect.

The table has been filtered by the first column. 

I assume need to use HASONEFILTER formula but not sure how...

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

This is a common problem about the measure total is incorrect, because measures are calculated based on context although in the total row, it's not a simply sum like a calculated column.

The general solution about this problem is to create a variable table in the measure like this:

Churn Measure =
VAR __Churn = [Value in GBP on last day of Month (2 Months ago) V1] - [Value in GBP on last day of Month (Last Month) V1]
VAR _M =
    IF ( __Churn > 2500, __Churn, 0 )
VAR _Table =
    ADDCOLUMNS ( 'Table', "Measure", _M )
RETURN
    IF (
        HASONEVALUE ( 'Table'[CustomerReference] ),
        _M,
        SUMX ( _Table, [Measure] )
    )

Additionally, here're more detailed discussion about this problem in this blog:

See this post that explains it, Dealing with Measure Totals

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.

Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.

Or this Quick Measure submission, Table Matrix Totals or Subtotals

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alena2k]

@Anonymous yes, you can check if your source table was filtered by customer and return different measure like this

Churn Measure 2 = 
var tmp1 = [Churn Measure]
var tmp2 = [Another Measure]
RETURN IF(HASONEVALUE('Table'[CustomerReference)), tmp1, tmp2)

However I THINK that your measure requires Customer context to work. Try to see if SUMX helps:

Churn Measure X = SUMX('Table', [Churn Measure])