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('Dat...
Need Help
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)
		)

       

      • OscLar's avatar
        OscLar
        Helper I
        8 years ago

        Hi,
        New to PBI and I hope it's ok that I continue this topic.

         

        Have a question for Phil_Seamark

         

        I have a very similar situation as the original question though I'm trying to rank days of a year from 1-364 (yes 364, I'm missing the last day of the year).

         

        However I run into trouble when multiple days have the same numerical value I want to rank. For instance I have two days, April 23 and October 15, both with a value of 2034 and they each get assigned rank number 42. And there are a few other such instances. This means that I end up with a table of 360 distinct rank values, where I'd hoped to have 364.

         

        I've tried adding filtering options to your "Ranking on week sales" to get around this problem but I can't seem to figure it out. Let's say I want the date which comes first in the year (April 23) to have rank 42, and October 15 to get rank 43.

         

        Can you pleas help me, or point me in the right direction.

         

        Cheers,

         

        Oscar