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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper I rankHelper 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

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi OscLar

         

        If you send me some sample data I can tweak the calculation to work for you :)

    • girishsinghal's avatar
      girishsinghal
      New Member
      6 years ago

      When I apply the first calculated column and create a table visual I get the below data.

      PlatformWeek Sales
      iPC6841888
      MS17145728
      P4S7145728

      Can you please help me understand how I got this?

       

      Regards,

      Girish