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
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

There is now a new tool in town - OFFSET. Consider that too.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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