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

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi OscLar

 

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

  • OscLar's avatar
    OscLar
    Helper I
    8 years ago

    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