Forum Discussion

Petersfield's avatar
Petersfield
Regular Visitor
9 years ago

Find the Second Highest Value

Hi All,

 

I am new to DAX and am having trouble pulling the second largest value for a filter function.

 

I am currently using the following the calculate the number of sales:

CALCULATE(SUM('Database Export'[Nb Sales]),(FILTER('Database Export', 'Database Export'[Weeks Since Launch]=MAX('Database Export'[Weeks Since Launch]))))

Where "Weeks Since Launch" is just a standard integer.

 

However my data source has changed it's formatting slightly and I now need to take the second highest value from the weeks since launch and not the highest. Is there an easy way to pull this number? I'm assuming I'd need something that functions similarly to a 'LARGE' command in excel?

 

Thanks in Advance

Need Help

7 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi Petersfield

     

    I'm wondering if we can take advantage of the RANK function and then look for items where RANK = 2 ???

     

    Do you have some sample data we can try this on?

    • Petersfield's avatar
      Petersfield
      Regular Visitor
      9 years ago

      Hey Phil!

       

      Sure, here's an example below of one product for one territory. What I would be looking to do is display is the Sum of the second highest week since launches sales, and then if possible the percentage change between the second higest and third highest week since launch. 

       

      Thanks!

       

       

      DatePlatformTitleSales TerritoryMeasure NameSalesWeeks Since Launch
      20/03/2017MS1WDD2.1United StatesWAU4201619
      13/03/2017MS1WDD2.1United StatesWAU11846418
      06/03/2017MS1WDD2.1United StatesWAU11446417
      27/02/2017MS1WDD2.1United StatesWAU12169616
      20/02/2017MS1WDD2.1United StatesWAU13699215
      13/02/2017MS1WDD2.1United StatesWAU12992014
      06/02/2017MS1WDD2.1United StatesWAU15907213
      30/01/2017MS1WDD2.1United StatesWAU17532812
      23/01/2017MS1WDD2.1United StatesWAU18355211
      16/01/2017MS1WDD2.1United StatesWAU19193610
      09/01/2017MS1WDD2.1United StatesWAU2083849
      02/01/2017MS1WDD2.1United StatesWAU2657928
      26/12/2016MS1WDD2.1United StatesWAU3168967
      19/12/2016MS1WDD2.1United StatesWAU1925446
      12/12/2016MS1WDD2.1United StatesWAU1045445
      05/12/2016MS1WDD2.1United StatesWAU1167364
      28/11/2016MS1WDD2.1United StatesWAU1352963
      21/11/2016MS1WDD2.1United StatesWAU1606722
      14/11/2016MS1WDD2.1United StatesWAU1283201
      20/03/2017P4SWDD2.1United StatesWAU4902419
      13/03/2017P4SWDD2.1United StatesWAU12985618
      06/03/2017P4SWDD2.1United StatesWAU13040017
      27/02/2017P4SWDD2.1United StatesWAU14880016
      20/02/2017P4SWDD2.1United StatesWAU16748815
      13/02/2017P4SWDD2.1United StatesWAU14649614
      06/02/2017P4SWDD2.1United StatesWAU18448013
      30/01/2017P4SWDD2.1United StatesWAU19916812
      23/01/2017P4SWDD2.1United StatesWAU21334411
      16/01/2017P4SWDD2.1United StatesWAU23513610
      09/01/2017P4SWDD2.1United StatesWAU2401289
      02/01/2017P4SWDD2.1United StatesWAU3125128
      26/12/2016P4SWDD2.1United StatesWAU3779527
      19/12/2016P4SWDD2.1United StatesWAU2459206
      12/12/2016P4SWDD2.1United StatesWAU1464005
      05/12/2016P4SWDD2.1United StatesWAU1635524
      28/11/2016P4SWDD2.1United StatesWAU1921603
      21/11/2016P4SWDD2.1United StatesWAU2242562
      14/11/2016P4SWDD2.1United StatesWAU1755201
      20/03/2017iPCWDD2.1United StatesWAU371219
      13/03/2017iPCWDD2.1United StatesWAU1011218
      06/03/2017iPCWDD2.1United StatesWAU1177617
      27/02/2017iPCWDD2.1United StatesWAU1276816
      20/02/2017iPCWDD2.1United StatesWAU1590415
      13/02/2017iPCWDD2.1United StatesWAU1750414
      06/02/2017iPCWDD2.1United StatesWAU1795213
      30/01/2017iPCWDD2.1United StatesWAU2124812
      23/01/2017iPCWDD2.1United StatesWAU2329611
      16/01/2017iPCWDD2.1United StatesWAU3334410
      09/01/2017iPCWDD2.1United StatesWAU338249
      02/01/2017iPCWDD2.1United StatesWAU404488
      26/12/2016iPCWDD2.1United StatesWAU508487
      19/12/2016iPCWDD2.1United StatesWAU406086
      12/12/2016iPCWDD2.1United StatesWAU367685
      05/12/2016iPCWDD2.1United StatesWAU450244
      28/11/2016iPCWDD2.1United StatesWAU451523
      21/11/2016iPCWDD2.1United StatesWAU2242
      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi Petersfield

         

        Sorry for the delay in replying

         

        Please try adding the following 2 calculated columns to your table

         

        Week Sales = CALCULATE(SUM('Table1'[Sales]),ALLEXCEPT('Table1',Table1[Date]))

        and

         

        Ranking On Week Sales = 
VAR 
    CurrentWeekSales = 'Table1'[Week Sales]
RETURN 
    COUNTROWS(
        FILTER(
            ALL(Table1[Week Sales]),
            'Table1'[Week Sales] < CurrentWeekSales)
            ) + 1

        This assigns a ranking value to each week based on the sum of sales.

         

        You can then build the following calculated measure on your table that use the above column (repeat for 3rd highest week) or just use the column above.

         

        Sum of second highest week = 
CALCULATE(
	SUM('Table1'[Sales]),
	FILTER(
		'Table1',
		'Table1'[Ranking On Week Sales] = 2)
		)