Get top 2 values across selected period

[Anonymous]

Hi, hopefully someone can help me with this.

I recently raised a ticket regarding how to get the top 1 value from a selected period (see below) however I am struggling to get the top 2.

https://community.powerbi.com/t5/Desktop/Measure-to-get-Value-with-the-highest-SUM/m-p/1395638#M5929...

My issue is I have a table that looks like the below

Date	Description	Calls
01/01/2020	Legal	2362
01/01/2020	ID	345
01/01/2020	Helpline	723
02/01/2020	Legal	1023

Now if I am only looking at one day, the below dax works fine - however if I move my slider to look across multiple days, "Legal" would appear twice as the Description with the most Calls - this is because it had the most on both 01/01/2020 and 02/01/2020. Based on the above table, I would want my first measure  (MostCommon) to show "Legal" as it would have a combined Call count of 3385. The second measure (SecondMostCommon) would be Helpline with a Call Count of 723.

This is the query I use for the Most Common measure however I can't seem to get it to work for the second most common in the period?

CALCULATE (
VALUES ( 'ReportTable'[Description] ),
FILTER ( ALL ('ReportTable'[Calls] ), 'ReportTable'[Calls] = MAX ( 'ReportTable'[Calls] ) )

[v-lili6-msft]

hi  @Anonymous 

Not clear for your expected output, What is your expected based on this sample data?

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[vanessafvg]

can you provide some sample data?

[Anonymous]

Hi hopefully this is useful?

Date	Description	Calls
01/01/2020	Legal	2354
01/01/2020	IT	273
01/01/2020	Helpline	27
02/01/2020	Legal	1389
02/01/2020	IT	334
02/01/2020	Helpline	23
03/01/2020	Legal	1400
03/01/2020	IT	321
03/01/2020	Helpline	46

[vanessafvg]

there are many ways to do it, and this probably isn't the most elegant solution but effectively you need to rank your values and use that ranking to drive the results you want.  see attached.

[Anonymous]

Hi thank you for this it was a great help in getting me to understand ranking!

All I need now is a measure that just shows me the [Description] where the Rank is 1 and another where the rank is 2.

I have tried the below but it isn't giving me the correct answer

Any ideas on how to get this working?

CALCULATE(FIRSTNONBLANK(ReportTable[Description],1),FILTER(ReportTable, [rank] = 1))