Ranking for Each Resource for each group

[Kumar11109]

Dear All, 

I need to tag the number of times each resource is accessed by each.  This means that for each group, and for all the resources the tagging should start from 1. I am attaching an image of the data set. Each time a resource is accessed, its start time would change. 

[Abduvali]

Hi @Kumar11109,

Create new measure using Rank function:

• Rank = RANKX(ALL(A[Customer]), A[Total Sales], ,DESC)       //alter to your table and fields

Ranking customers by Sales value

 If you would like to know more about Rank function refer to the link below:

• Dynamic TopN & Ranking in Power BI

Hope this helps.

Regards

Abduvali

[Kumar11109]

NOPE, STILL THE SAME . :-c

[Abduvali]

@Kumar11109,

Can you give me some sample data? and I will help you out...

[Kumar11109]

Here you go

Link

[Abduvali]

@Kumar11109,

I see whats happening, see the way you have your StartDate and EndDate there and they all Unique because of a time stamp!

• but if you drop your date it will rank your Group by ResourcesID
• or try to group your date by months:
  • Month = Month('final Score Table'[StartDateTime]) and then you this in your column

Hope this explains the issue.

Regards

Abduvali

[Kumar11109]

@Abduvali

Can you check the sheet again? I have done tagging for each resource, for each group. I need it in that form. I have done it on only first 100 rows. And I didn't understand the solution that you gave me. 

Another point I can't drop any column, I need them for other calculations. 

[Zubair_Muhammad · Accepted solution]

HI @Kumar11109

You first need to add an INDEX column before you could do the RANKING

See the attached file with your data

Step # 1: Add Index Number

Step#2 Ranking

Now you can RANK using this calculated column

RANK_ =
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[GroupId] = EARLIER ( Table1[GroupId] )
            && Table1[ResourceId] = EARLIER ( Table1[ResourceId] )
    ),
    Table1[Index],
    ,
    ASC,
    DENSE
)

[Anonymous]

Hi @Zubair_Muhammad ,

Can you suggest how to add the column "RANK" in power query as indicated in image ?

[Zubair_Muhammad]

@Kumar11109

See attached file here

(With your data)

[Kumar11109]

@Zubair_Muhammad

Thank You for your help. Now I am facing another problem. I need to create a scatter plot to check whether with each attempt students score is increasing or not. So for x axis, I need to put in the attempt number" 1,2,3,4....." and for the y axis, I need marks scored in that attempt. But the issue with Power bi is that its aggregating the attempt number( Rank) and the marks scored. I don't need aggregates but the option to don't summarize. How would be able to do that???

Thank You, 

[Kumar11109]

@Abduvali

No, it's not working. I am getting the same rank value for all the groups and its resources. Look at the attached picture. 

[Abduvali]

@Kumar11109,

For rank to work you have to count the ResourceID's:

Rank = rankx(all 'Final Score Table'[GroupID], ---> here you need to add a measure that counts your ResourceID so create it something like ResourceID Count = COUNT('Final Score Table'[ResourceID]) and then use it in DAX formula:

• Rank = RANKX(ALL( 'Final Score Table'[GroupID]),[ResourceID Count], , Desc)

This should work now.

Regards

Abduvali