Division within Matrix rows

[Beyeservices]

Hi Guys,

I want to divide a Matrix row with the line above it in Power BI and I coudnt find a solution to it. please look at the below pivot table in the below link. i couldnt attach file so i am put that on Google sheets.

https://docs.google.com/spreadsheets/d/1nD0H12eUBbtV5YaBrMiswKLpGlhoOYQ5eM4Et_yOx8A/edit?usp=sharing

I need the second table to dynamically respond to the first table. In the file columns are 'country' but if I re place it with Region it should change dynamically. It can be easily done in excel but I am struggling to show that in Power BI.

Any help is appreciated

[v-eachen-msft]

Hi @Beyeservices ,

You could refer to the following measure.

Measure =
VAR a =
    CALCULATE (
        [Your value expression],
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Stage]
                = MAX ( 'Table'[Stage] ) - 1
                && 'Table'[Country] = MAX ( 'Table'[Country] )
        )
    )
RETURN
    IF ( a = BLANK (), BLANK (), [Your value expression] / a )

[amitchandak · Accepted solution]

@Beyeservices , we do not have any method two subtract from last row

but we can do like this

This Stage= CALCULATE(sum('order'[Qty]),filter(ALL('Stage'),'Stage'[Stage]=max('Stage'[Stage])))
Last Stage= CALCULATE(sum('order'[Qty]),filter(ALL('Stage'),'Date'[Stage]=max('Stage'[Stage])-1))

diff = [This Stage] -[Last stage]

if they not numeric we can have rank on them that can work

This Stage= CALCULATE(sum('order'[Qty]), FILTER(ALL('Stage'),'Stage'[StageRank]=max('Stage'[StageRank])))
Last Stage= CALCULATE(sum('order'[Qty]), FILTER(ALL('Stage'),'Stage'[StageRank]=max('Stage'[StageRank])-1))