rank top 2 scores in horizontal table

[primolee]

Hello everyone,

I have one table with daily TV GRP of different car brands of different TV channels.  With built-in visualization, following table is created.

I want the top 2 channels with the highest total GRP of each car brand by month displayed horizontally like the following image:

I was thinking of creating a new table with car brand names only and then use rankx.  But I can't get it to work.  Could someone please help me on this?  Thank you so much for your time and help.

Here is a sample pbix file.

https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing  

Best regards,

David

[v-yangliu-msft]

Hi  @ primolee ,

Here are the steps you can follow：

1. Create a calculate table.

Sum =
SUMMARIZE('Daily GRP',
[Car Brand],[Channel Group],[Month],
"Sum",SUM('Daily GRP'[GRP]))

2. Create measure.

Rank =
RANKX(
    FILTER(
        ALL('Sum'),
        [Car Brand]=MAX([Car Brand])),
        CALCULATE(MAX([Sum])),,DESC,Dense)

Channel with No.1 GRP =
CALCULATE(MAX('Sum'[Channel Group]),FILTER('Sum','Sum'[Rank]=1) )

Channel with No.2 GRP =
CALCULATE(MAX('Sum'[Channel Group]),FILTER('Sum','Sum'[Rank]=2) )

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@primolee , I suggested a Column Rank approch .

Create these new columns

Rank Col = rankx(filter(Table, [Car Brand] = earlier([Car Brand])),[GRP] , ,desc,dense)
Rank Desc ="Channel with GRP No." & [Rank Col] & " GRP"

Then use Rank Desc on Matrix Column and Car Brand on row and Channel (first/last) as value

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[primolee]

Hello @amitchandak 

Thank you so much for your prompt reply.

Sorry but maybe I did not make myself clear.  My table contains daily GRP information of several months.  Creating new columns like this will have ranking by daily, I want to compare by month so this matrix would not work.  Moreover, I want to use drop-down filter to select multiple months so that the ranking would change dynamically by summing up the subtotal of GRP.

Following link is the sample I created.

https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing 

I am not sure if I made myself clear enough.  Please give me a hand on this, thank you!

Best regards,

David

[primolee]

Anyone has any idea?

Following is a sample of my pbix, please give me a hand on this, thank you.

https://drive.google.com/file/d/1GdQ72xFYuSIi9AhPntUqa2mpbgieNxdS/view?usp=sharing 

[v-yangliu-msft]

Hi  @ primolee , 

Here are the steps you can follow： 

1. Create a measure to calculate the daily ranking 

Rank_Day =  
RANKX( 
    FILTER( 
        ALL('Table'), 
    'Table'[Date]=MAX('Table'[Date])), 
    CALCULATE(MAX('Table'[Grp])),,ASC,Dense) 

 2. Create a column to calculate the monthly total, create a measure to calculate the monthly ranking 

Rank_Month_Mount =  
RANKX( 
        ALL('Table'), 
    CALCULATE(MAX('Table'[Month_mount])),,ASC,Dense) 

 3. Result. 

You can downloaded PBIX file from here. 

Best Regards, 

Liu Yang 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[primolee]

Hello @v-yangliu-msft 

Thank you so much for your help.  I am very sorry but it is still different from what I wanted to do.  Sorry I did not explain clearly enough previously.

There are daily GRP of many different car brands in many different months.  I want to find the top 2 TV Channels with the top 2 monthly-total GRP by each brand in each month.  Not of a specific date in that month, but the total GRP of that month.

In the example you provided, if I select Month 9, this would be the result:

If I select Month 10, this would be the result:

Is there a way to achieve this?  Thank you so much for your time and help.

Best regards,

David