Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Petersfield
Regular Visitor

Find the Second Highest Value

‎03-27-2017 06:26 AM

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

Labels:
Message 1 of 8
41,590 Views
0
7 REPLIES 7
Phil_Seamark
Employee
Employee

‎03-27-2017 01:23 PM

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?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 2 of 8
41,576 Views
Petersfield
Regular Visitor
In response to Phil_Seamark

‎03-28-2017 06:53 AM

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
Message 3 of 8
41,562 Views
0
Phil_Seamark
Employee
Employee
In response to Petersfield

‎03-29-2017 03:00 PM

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)
		)

 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 4 of 8
41,547 Views
0
girishsinghal
New Member
In response to Phil_Seamark

‎06-09-2020 11:20 AM

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

Message 8 of 8
30,973 Views
0
OscLar
Helper I
Helper I
In response to Phil_Seamark

‎02-14-2018 01:50 AM

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

Message 5 of 8
40,192 Views
0
Phil_Seamark
Employee
Employee
In response to OscLar

‎02-14-2018 02:09 PM

Hi @OscLar

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 6 of 8
40,180 Views
0
OscLar
Helper I
Helper I
In response to Phil_Seamark

‎02-15-2018 04:29 AM

Morning,

 

Thought I could attache a file in a privat message but cant't find th option so here comes som data from me.

 

I've copied your ranking code but in any case this is my code:

 

Rank A -> B = 
var currentDay = 'Datum'[Yearly Max Flow A -> B]
return
COUNTROWS(
    FILTER(
        ALL('Datum'[Yearly Max Flow A -> B]);
        'Datum'[Yearly Max Flow A -> B] > (currentDay ) )
) +1

This gives the highest value in column [Yearly Max Flow A -> B] rank 1, and so on.

 

 

Date	Yearly Max Flow A -> B	Yearly Max Flow B -> A
2017-05-19 00:00	2572,833333	1863,5
2017-09-21 00:00	2562,833333	1678,5
2017-03-24 00:00	2366,666667	1724,25
2017-09-01 00:00	2255,25	1877
2017-08-22 00:00	2240,083333	1914,5
2017-09-26 00:00	2215,916667	2158,5
2017-06-22 00:00	2193,166667	1515
2017-04-03 00:00	2190,083333	1991
2017-11-26 00:00	2187,5	2110,5
2017-09-08 00:00	2182,166667	1582,5
2017-05-16 00:00	2181,333333	1874,25
2017-09-29 00:00	2176,166667	1771
2017-07-28 00:00	2152,666667	1644,5
2017-09-22 00:00	2138,25	1676,583333
2017-04-27 00:00	2136,75	1798,5
2017-06-30 00:00	2129,5	1687,5
2017-10-01 00:00	2122,25	1657
2017-12-11 00:00	2118	1666,25
2017-05-04 00:00	2117,833333	1978,25
2017-09-06 00:00	2108,583333	1736,5
2017-09-24 00:00	2098,75	1874,666667
2017-06-29 00:00	2095,916667	1502,5
2017-09-18 00:00	2094,583333	1603,25
2017-06-08 00:00	2093,916667	1851,5
2017-03-28 00:00	2093,25	2247,166667
2017-10-16 00:00	2086,75	1831
2017-10-06 00:00	2085,333333	1543,916667
2017-06-16 00:00	2080,25	1941
2017-12-05 00:00	2076	2075,25
2017-06-04 00:00	2072,25	1537,083333
2017-07-02 00:00	2065,083333	1399,333333
2017-05-14 00:00	2064,416667	2082,333333
2017-11-15 00:00	2063,75	1918,75
2017-08-10 00:00	2060,5	1539,166667
2017-09-11 00:00	2056,666667	1716,25
2017-06-18 00:00	2056,5	1500
2017-04-07 00:00	2050,416667	1778,75
2017-11-06 00:00	2049,75	1986,5
2017-11-27 00:00	2044,333333	1791,5
2017-09-13 00:00	2044,25	1864,5
2017-04-09 00:00	2041,75	2089,25
2017-04-23 00:00	2034	1611,5
2017-10-15 00:00	2034	1481

These are the 43 highest values in the column A -> B (Middle) however as acn be seen, the last two entries are equal (2034) and thus with "my" code both get rank 42.

 

 

Thanks for the help!

 

Cheers,

 

Oscar

Message 7 of 8
40,171 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All