add a calculated column

[reemadsouza]

I want to add a calculated column in the below matrix, which will calculate, if row =202, then take value in row 201 and divde by value in row 75, it should dynamically calculate with changes to comapny , geo, or period and the month/years

[pankajnamekar25 · Accepted solution]

Hello @reemadsouza 

try this DAX

NewColumn =

VAR CurrentMetric = 'Data'[MetricID]

VAR Company = 'Data'[Company]

VAR Geo = 'Data'[Geo]

VAR Period = 'Data'[Period]

VAR Value201 =

    CALCULATE (

        MAX ( 'Data'[Value] ),

        'Data'[MetricID] = 201,

        'Data'[Company] = Company,

        'Data'[Geo] = Geo,

        'Data'[Period] = Period

    )

VAR Value75 =

    CALCULATE (

        MAX ( 'Data'[Value] ),

        'Data'[MetricID] = 75,

        'Data'[Company] = Company,

        'Data'[Geo] = Geo,

        'Data'[Period] = Period

    )

RETURN

    IF (

        CurrentMetric = 202,

        DIVIDE ( Value201, Value75 )

    )

Thanks,
 Pankaj Namekar | LinkedIn