topic Re: Need Help with DAX formula in DAX Commands and Tips

Need Help with DAX formula

sankybadlapur — Wed, 08 May 2024 11:17:20 GMT

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)

Re: Need Help with DAX formula

lbendlin — Wed, 08 May 2024 22:36:24 GMT

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

Re: Need Help with DAX formula

sankybadlapur — Thu, 09 May 2024 04:59:32 GMT

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

Re: Need Help with DAX formula

lbendlin — Fri, 10 May 2024 00:04:22 GMT

What is the field uniquely identifying each row?

Re: Need Help with DAX formula

sankybadlapur — Fri, 10 May 2024 06:33:43 GMT

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