Forum Discussion
add a calculated column
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
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
1 Reply
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