Forum Discussion

sankybadlapur's avatar
sankybadlapur
New Member
2 years ago
Solved

Need Help with DAX formula

Hi Friends,   I am trying to get second largest value of sum of selected rows. Currently i am able to get only 2nd largest value of the selection with below dax, however not able to achive the outp...
  • sankybadlapur's avatar
    sankybadlapur
    2 years ago
    Hello, Thanks for the reply. Got the solution with designing below dax. Pushing here so that our friends can take reference if required. test = VAR SelectedMonth = SELECTEDVALUE('CY KPI'[Month]) RETURN VAR RowSums = ADDCOLUMNS( 'CIP', "Row_Sum", SUMX( FILTER('CIP', EARLIER('CIP'[CIP Description]) = 'CIP'[CIP Description]), 'CIP'[New_Temp_YTD_Actual] ) ) VAR MaxRowSum = CALCULATE( MAXX(RowSums, [Row_Sum]), 'CY KPI'[Month] = SelectedMonth ) VAR SecondHighestRowSum = CALCULATE( MAXX( FILTER(RowSums, [Row_Sum] <> MaxRowSum), [Row_Sum] ), 'CY KPI'[Month] = SelectedMonth ) RETURN SecondHighestRowSum
sankybadlapur's avatar
sankybadlapur
New Member
2 years ago

Hi lbendlin,

Thanks for the reply. Pasting data below for reference. I need to calculate sum of actuals for each row. And from that sum and need 2nd highest sum. For eg. July Actuals + Aug Actuals + Sept Acuals for each row and from that each row sum need 2nd highest. Months are selected by the user from navigation page.

July Aug
PlannedActualPlannedActualPlannedActual
      
      
      
      
$5,600  $6,753  $5,600  $5,757  $7,000  $6,351 
  $56,445   $82,003   $116,944 
  $6,794   $6,794   $6,794 
  $6,670   $6,468   $13,980 
    $28,949   $25,109 

 

Thanks in Advance!!

 

Sanket

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

What is the field uniquely identifying each row? 

  • sankybadlapur's avatar
    sankybadlapur
    New Member
    2 years ago
    Hello, Thanks for the reply. Got the solution with designing below dax. Pushing here so that our friends can take reference if required. test = VAR SelectedMonth = SELECTEDVALUE('CY KPI'[Month]) RETURN VAR RowSums = ADDCOLUMNS( 'CIP', "Row_Sum", SUMX( FILTER('CIP', EARLIER('CIP'[CIP Description]) = 'CIP'[CIP Description]), 'CIP'[New_Temp_YTD_Actual] ) ) VAR MaxRowSum = CALCULATE( MAXX(RowSums, [Row_Sum]), 'CY KPI'[Month] = SelectedMonth ) VAR SecondHighestRowSum = CALCULATE( MAXX( FILTER(RowSums, [Row_Sum] <> MaxRowSum), [Row_Sum] ), 'CY KPI'[Month] = SelectedMonth ) RETURN SecondHighestRowSum