Compare Column in a matrix

[polman4]

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

[v-yalanwu-msft]

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.

[v-yalanwu-msft]

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.

[polman4]

@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]

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]

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 · Accepted solution]

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]

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!