Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Row level total in matrix incorrect for a measure

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

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

2 Replies

  • alena2k's avatar
    alena2k
    Icon for Resolver IV rankResolver IV
    3 years ago

    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])
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.