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 output. 

Need to do the sum of the rows and from that sum i need to get 2nd larget sum.

This Dax is giving me only 2nd largest value

test =
VAR secondvalue = MAX(CIP[New_Temp_YTD_Actual])
RETURN
CALCULATE(MAX(CIP[New_Temp_YTD_Actual]),REMOVEFILTERS(CIP),CIP[New_Temp_YTD_Actual]<secondvalue)

Tried with creating column in varible but cant understad how do i use that in dax. Request you friends to help!!:

test =
VAR RowSums =
    ADDCOLUMNS(
        CIP,
        "Row_Sum", SUMX(CIP, CIP[New_Temp_YTD_Actual])
    )
VAR secondvalue = MAX(CIP[New_Temp_YTD_Actual])
RETURN
CALCULATE(MAX(CIP[New_Temp_YTD_Actual]),REMOVEFILTERS(CIP),CIP[New_Temp_YTD_Actual]<secondvalue)
  • 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

4 Replies

  • 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?