Dynamic Ranking Measure

[carlochecchia]

Hi,

I was wondering if I could get some help in order to set up my report.

I have data for 6 Twitter WebPages.

The dataset contains information on the WebPage, Type, and Followers  as per table below.

WeBPage	Type	Followers	Data
ABC	MEDIA	12.800.869	08/06/2021
DEF	MEDIA	8.675.291	08/06/2021
GHI	MEDIA	7.910.907	08/06/2021
JKL	TV	1.480.418	08/06/2021
MNO	TV	643.256	08/06/2021
OPQ	TV	531.600	08/06/2021

I created a measure to rank the WebPage based on the number of followers.

Additionaly, i created a column[Type.WebPage] that concatenates the columns "Type" & "WebPage" to use as a slicer in my report.

The result I would like to achieve is the following:

When I select "MEDIA - DEF" from the slicer [Type.WebPage], i should get the result "2".
Because its the WebPage with the 2nd highest number of followers in the criteria "Media".

If I select "TV - JKL", i should get the result 1.

How can I write the measure to get the dynamic ranking based on the slicer.

Additionally, I would like to be able to create a ranking measure based on Type, as below

Please find below a sample data 

https://checonpesquisa-my.sharepoint.com/:u:/g/personal/carlo_checonpesquisa_com_br/EYooc7MBOJBAtBX2... 

Thanks in advance

[v-jingzhang · Accepted solution]

Hi @carlochecchia 

You can use RANKX function to create a calculated column. This would be easy.

Rank Column = 
RANKX (
    FILTER (
        'Twitter',
        'Twitter'[Type] = EARLIER ( 'Twitter'[Type] )
            && Twitter[Data] = EARLIER ( Twitter[Data] )
    ),
    Twitter[Followers],
    , // leave value argument blank
    DESC
)

 

Then create a measure to get the rank column value. 

Ranking Measure = SELECTEDVALUE(Twitter[Rank Column])

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[carlochecchia]

@v-jingzhang ,

Thank you for your reply. It worked great.

Can you please assist with a couple more questions, please?
Sometimes I may have more than one record, per webpage, computed in my database.
How can my rank take in account only the latest followers count, for the earliest day selected?
What I would like is to see the rank as #1 for ABC - Media, when August 6th is selected.

Additionaly, i inserted data for WebPAGE ZPQ, on August 6th, 2021. Followers count is 10.010.907 as per image below.

I case I select August 10th, 2021 and filter by 'MEDIA - ZPQ', I would like to see the result as #2 for the ranking. Since its the WebPage with the second most amount of followers 
For the dates where there is no Data available, i would like to take in account the data for the last day that it was available. Is it possible?

Thanks in advance

Here is the sample file, in case needed

https://checonpesquisa-my.sharepoint.com/:u:/g/personal/carlo_checonpesquisa_com_br/Eb-uVdCic8tPjeFY...

[v-jingzhang]

Hi @carlochecchia 

I have one question that hope you can make it clearer.

When you have more than one record per webpage on the same day, just like ABC-MEDIA, which record of followers is the latest record we should take into account? In the Data column, there is only date value without exact time value, so I cannot tell which one is the latest. Maybe the largest followers record is the one you need? Is there any possibility that the latest record has a smaller value? Or maybe you can add exact time values of all records?

[carlochecchia]

@v-jingzhang once again thank you for getting back to me with this matter. The original data has the date and timestamp in it. Im sorry i didnt include it in the sample file I sent you. ( I have updated it and included the column DateTime. The link remains the same.
I would like to use the latest available data for the end of the day.
Best Regards

[v-jingzhang]

Hi @carlochecchia 

I create a new table in Power Query Editor based on original table to fill in blank values on dates which have no values and remove non-latest rows on dates which have multiple values. The result is as below. You can then use this new table in the report to get the rank value. I keep the original table in the model too. 

I believe there is DAX method to get what you want, but I don't know how to do that. 😬

Jing

[carlochecchia]

Hi @v-jingzhang ,

Thank you for getting back to me with an answer. It worked great. If possible, can you help me with the following question:
1. Most of the times I have several records, from the same account, during the same day.
How can I rank based only on the WebPage and Latest Date where records are available?

Please see the image below.
If WebPage ABC has two records for the same day, how can i take in account for ranking purposes the last available data only?
Instead of showing rankings '2' and '1', it should show only 1, since the ranking is based on the WebPage

Additionally, Ive inserted data for WebPage ZPQ - Media. Lets pretend I only have a record for August 6th - 2021 and the followers count was 10.010.907.

When I select the filter for August 10th, and the WebPage Media - ZPQ, i would like to see the result as number 2 (for the ranking) . Since the last available record was from August 6th and the webpage has 10.010.907 followers.

Please see the sample data, in case necessary,

Thanks in advance

https://checonpesquisa-my.sharepoint.com/:u:/g/personal/carlo_checonpesquisa_com_br/Eb-uVdCic8tPjeFY...