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