Need Help with DAX formula

[sankybadlapur]

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)

[lbendlin]

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

[sankybadlapur]

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
Planned	Actual	Planned	Actual	Planned	Actual
$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]

What is the field uniquely identifying each row? 

[sankybadlapur · 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