cancel
Showing results 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

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 =
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
New Member
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 4
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.

New Member

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

Sanket

Super User

What is the field uniquely identifying each row?

New Member
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