Forum Discussion
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 outp...
- 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
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