Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Ranking Data

Hi,

I'm trying to add a rank to the below table in PBi. Each colum (Month,Team&Emplyee,Sumber of solves are in seperate tables)

The rank would need to rank each employee by team by month. So each team would have it's own sperate rank each month.

Tried doing this a couple of times to no avail. Any help would be appreciated.

 Month Team Employee Number of solves Rank April Team1 Person1 20 April Team2 Person2 30 April Team3 Person3 48 April Team4 Person4 65 April Team5 Person5 70 April Team1 Person6 2 April Team2 Person9 3 May Team3 Person3 4 May Team4 Person4 5 May Team5 Person5 6 May Team1 Person7 7 May Team2 Person2 8 May Team3 Person8 69 May Team4 Person10 56 June Team5 Person5 26 June Team1 Person1 695 June Team2 Person2 23 June Team3 Person3 5 June Team4 Person4 545 July Team5 Person5 5647

Thanks

2 ACCEPTED SOLUTIONS
Super User

Hi,

Please check the below picture and the attached pbix file.

``````Number of solves: =
SUM ( Data[Number Of Solves] )``````

``````Ranking per Month and per Team: =
VAR employeetable =
ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )
RETURN
IF (
HASONEVALUE ( Employee[Employee] ) && NOT ISBLANK ( [Number of solves:] ),
RANKX ( employeetable, [Number of solves:],, DESC )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Frequent Visitor

Hi,

In your example:

"ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )"

I don't have a table named "data". All the tales are joined using a relationship.

Do I need to look at Crossjoin in order to get this to work?

Thanks,

12 REPLIES 12
Frequent Visitor

Hi again,

Got it working using a summazried table.

"

Solved/Closed Ticket Ranking =
VAR Currentmonth = 'Summary Table'[MonthYear]
VAR Currentteam = 'Summary Table'[Team]
VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)

RETURN

rankx(newtable,'Summary Table'[Numer of Solved/Closed Tickets],,DESC,Dense)"

Is the code above how could I make it so the rank ignore blanks?

Thanks,

Stu
Frequent Visitor

Hi again,

Got it working by creating a summary table. Using your code earlier.

Quick question, how can i make so the ranking ignore blanks (within the "

'Summary Table'[Number of Tickets]" column?

"

Solved/Closed Ticket Ranking =
VAR Currentmonth = 'Summary Table'[MonthYear]
VAR Currentteam = 'Summary Table'[Team]
VAR newtable = filter('Summary Table','Summary Table'[MonthYear]=Currentmonth && 'Summary Table'[Team] = Currentteam)
RETURN

rankx(newtable,'Summary Table'[Number of Tickets],,DESC,Dense)"

Thanks,

Stu
Super User

Hi,

I am not sure how your expected outcome looks like, but please check the below picture and the attached pbix file.

It is for creating a new column.

``````Rank Employee by Team and by Month CC =
VAR currentmonth = Data[Month]
VAR currentteam = Data[Team]
VAR newtable =
FILTER ( Data, Data[Month] = currentmonth && Data[Team] = currentteam )
RETURN
RANKX ( newtable, Data[Number Of Solves],, DESC )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Frequent Visitor

Hi,

The outcome is perfect. Only issue is that these fields are not in the same table. The relate to one another.

Table examples, Month,Team/Employee,Count of solved. This means I cannot create a new column.

Would I need to create a new table?

Super User

Hi,

Thank you for your message.

Please correct me if I missed something from your question, but is it separated by month? or by team? How many separared tables do you have?

If it is OK with you, could you please show how the each table looks like?

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Frequent Visitor

Hi again, thank you for your time so far.

I guess what I'm asking for is what you've done so far but as a measure. If that is even possible.

DimCalendar[Month]

Company[Team]/Company[Emplyee]

Data[Number of Solves]

Each of the table have a relatopship between them.

Hope this makes sense.

Super User

Hi,

Please check the below picture and the attached pbix file.

``````Number of solves: =
SUM ( Data[Number Of Solves] )``````

``````Ranking per Month and per Team: =
VAR employeetable =
ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )
RETURN
IF (
HASONEVALUE ( Employee[Employee] ) && NOT ISBLANK ( [Number of solves:] ),
RANKX ( employeetable, [Number of solves:],, DESC )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Frequent Visitor

Hi,

I have copied this and with my version the rank is only ever 1 or blank.

Not sure as to why. I will try and investigate.

Thanks,

Frequent Visitor

Hi,

Cannot seem to figure out why it's not working. Any other suggestions?

Thanks,

Super User

Hi,

Please share your sample pbix file, and then I can try to have a look into it to come up with a more accurate solution.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Go to My LinkedIn Page

Frequent Visitor

Hi,

In your example:

"ALLEXCEPT ( Data, 'Calendar'[Month], Team[Team] )"

I don't have a table named "data". All the tales are joined using a relationship.

Do I need to look at Crossjoin in order to get this to work?

Thanks,

Frequent Visitor

Hi,

I believe the issue is I don't have a table named "data" where all this data resides. My data is in 3 sperate tables (although they do have a relationship).

Thanks,

Stu

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors