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 is the usual i create:

 

What i want to do is after the compare a column data with the previous "column". For example after the column called 6/6/2022 to compare 186.702 to 176.011

I have figured out how to compare two different column or the in same column by previous month etc. But here is a custom column and don't know how to define "previous" column.

 

p.s. this is how i used to do it on excel.

 

Any help appreciated it!

thank you

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.

7 Replies

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

    Hi, polman4 ,

    You could add another measure,

    Measure = IF(ISINSCOPE('Table'[author]),[diff],SUMX( SUMMARIZE('Table',[author], [Column],"1",[diff]),[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.

    • polman4's avatar
      polman4
      Helper I
      4 years ago

      Hello v-yalanwu-msft 

      I did a review today on some data and I think the measure % is not correct. If i'm not mistaken, what you is to summarize the percentages above. But this isn't correct mathematically.

      Also if you do the calculation (211.51 / 194.66) -1  = 8,76. Not 39,07.

      Thank you!

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.

    • polman4's avatar
      polman4
      Helper I
      4 years ago

      Hello v-yalanwu-msft 

       

      Thank you for the detailed reply. Took almost a week to understand but was really helpfull and learned a lot.

      Quick question. I guess it can't work for totals?

       

      Thank you!

  • polman4's avatar
    polman4
    Helper I
    4 years ago

    v-yalanwu-msft Thank you a lot of the awesome solutions. Really helped me a lot learning new things. Got a last question if you can help.
    I have some columns that are measure so i made a small change to the measure: (used sumx)

     

    % PPA = 
VAR _max =
    CALCULATE (
        MAX ( 'nw_content'[Week_Start_Date] ),
        FILTER (
            ALL ( 'nw_content' ),
            [Author_Clm] = MAX ( [Author_Clm] )
                && [Week_Start_Date] < MAX ( [Week_Start_Date] )
        )
    )
VAR _pir =
    CALCULATE (
        SUMX(nw_content, nw_content[PPA]),
        FILTER ( ALL ( 'nw_content' ), [Author_Clm] = MAX ( [Author_Clm] ) && [Week_Start_Date] = _max )
        )
VAR _result =
    DIVIDE ( SUMX(nw_content, nw_content[PPA]), _pir ) - 1
RETURN
    IF ( _result = -1, "no data", _result )

     

    But the result doesn't seem to be correct.
    Here is the PPA measure:

     

    PPA = (sum('nw_content'[Pageviews])/ DISTINCTCOUNT('nw_content'[nw_c_pageuid]))

     

     

    Maybe i shouldn't use sumx?

     

    Thank you!

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

    Hi, polman4 ;

    Try it.

    % PPA =
VAR _max =
    CALCULATE (
        MAX ( 'nw_content'[Week_Start_Date] ),
        FILTER (
            ALL ( 'nw_content' ),
            [Author_Clm] = MAX ( [Author_Clm] )
                && [Week_Start_Date] < MAX ( [Week_Start_Date] )
        )
    )
VAR _pir =
    SUMX (
        FILTER (
            ALL ( 'nw_content' ),
            [Author_Clm] = MAX ( [Author_Clm] )
                && [Week_Start_Date] = _max
        ),
        nw_content[PPA]
    )
VAR _result =
    DIVIDE ( SUMX ( nw_content, nw_content[PPA] ), _pir ) - 1
RETURN
    IF ( _result = -1, "no data", _result )


    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.