Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sankybadlapur
New Member

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)
1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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

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/1447...

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

What is the field uniquely identifying each row? 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors