DAX Measure require for Ranking

[jamalq123]

Dear All,

I am associated with a channel business. I want to determine the rank of a particular program of a channel. Rank of the program should consider the timeslots and the date in which the program telecasted in a week, month, Quarter etc. Rank should be based on all the programs of the different channels which telecasted on the same time-slots and on the same date so that an apple to apple comparison could be done.

Here I have taken 2 programs of Geo named Noore- Zindagi and Dhani which attained ranking of 1 and 3 respectively. These programs have telecasted on the same time-slots but on the different date.

For this exercise, I use pivot table but it takes significant time. I complete the exercise in the following steps:

• In 1st phase, I have to identify that in which timeslots and the date, the program is telecasted. I jot down it in a copy.
• In 2nd phase, I fix the same time slots and date in Pivot table.
• Then run all the programs of all the channels on the above criteria
• Work out the Rank based on the rating.

For each program I have to do the same exercise, this is very lengthy process, kindly help me in this project.

I am also enclosing here dummy data of a month. Please see data here: https://drive.google.com/open?id=0B5eKgvOGu5MLaGt6M2Zidk9PY0k.

Kindly help me to make the DAX Measure which could be applied on any programs of any channel.

Regards,

Jamal Qamar

[v-qiuyu-msft]

Hi @jamalq123,

You an create two measures below: 

AVG = AVERAGEX(FILTER(ALLSELECTED('Rating data'),'Rating data'[Program]=MAX('Rating data'[Program])),'Rating data'[Ratings])

Rank = var t=RANKX(ALLSELECTED('Rating data'),[AVG],,DESC,Dense)
return IF(COUNTROWS('Rating data')=COUNTX(ALLSELECTED('Rating data'),'Rating data'[Ratings])||COUNTROWS('Rating data')=COUNTX(FILTER(ALLSELECTED('Rating data'),'Rating data'[Program]=MAX('Rating data'[Program])),'Rating data'[Ratings]),t,BLANK())

You can download attached to see if it meet your requirements. 

Best Regards,
Qiuyun Yu 

[jamalq123 · Accepted solution]

Dear All,

This was no doubt a complicate project for me, I got one response from the Power BI community but it was not meeting the requirement.

I thought to make any DAX measure to resolve the issue but not achieved the objective.

I spent a significant time on it and after discussion with my bro, I worked on the normalization and now it is working well. I still feel that there is some data redundancy, but this was the last approach which I used. Please download the PBIX file form the link and give your feedback which is very important for me and if any suggestion to improve it please guide me.

https://drive.google.com/open?id=0B5eKgvOGu5MLX1BqQ3F5aVFRNU0

Regards,

Jamal Qamar

[jamalq123]

Thanks for your response and very sorry for replying delay, 

Today based on the data which i shared with you and you also also woked on it and sent me the PBIX file. I have done ranking of  Geo TV programs manually based on the criteria which i mentioned earlier and the out put is given belowI.But when I comapare it with your PBIX file, it does not match with the one which i did manually. 

I feel that you are making comparison among the the programs of Geo. This is not my objective. But when i select other channel also it makes comparision on all the programs which are appearing but this is not my objective. 

I want to compare the programs which telecast on the same date and the same time-slots. 

If there is any confusion, kindly reply me. Moreover, the table which I made above it took around 45 minutes, I want to avoid it

 This is the snapshot of PBIX which I have taken for comparison purposes..

Regards,

Jamal Qamar