Forum Discussion

polman4's avatar
polman4
Helper I
4 years ago
Solved

Compare Column in a matrix

Hello,   I have a matrix with the following data Rows Author   Column Week Start Date = (nw_content[nw_c_publishdate]-WEEKDAY(nw_content[nw_c_publishdate],3)   Values Pageviews.   Below i...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， polman4 ，

    Create a measure ,

    diff = var _max=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),[author]=MAX([author])&& [Column]<MAX([Column])))
var _pir= CALCULATE(SUM([value]),FILTER(ALL('Table'),[author]=MAX([author])&&[Column]=_max))
var _result= DIVIDE(SUM([value]),_pir)-1
return IF(_result=-1,BLANK(),_result)

    Or create a column.

    differ = var _max=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),[author]=EARLIER([author])&& [Column]<EARLIER([Column])))
var _pir= CALCULATE(SUM([value]),FILTER(ALL('Table'),[author]=EARLIER([author])&&[Column]=_max))
var _result= DIVIDE([value],_pir)-1
return IF(_result=-1,BLANK(),_result)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

Hi, polman4 ;

diff = var _max=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),[author]=MAX([author])&& [Column]<MAX([Column])))
var _pir= CALCULATE(SUM([value]),FILTER(ALL('Table'),[author]=MAX([author])&&[Column]=_max))
var _total=CALCULATE(SUM([value]),FILTER(ALL('Table'),[Column]=_max))
var _result= DIVIDE(SUM([value]),_pir)-1
return  IF(_result=-1,BLANK(),IF(ISINSCOPE('Table'[author]),_result,DIVIDE(SUM([value]),_total)-1))

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.