RANKX with COUNT/SUM function

[_eNVy_]

Hi all,

Stuck with the following :

Name	Due Status	Active Overdue
Person 1	On Time	0
Person 1	Overdue	1
Person 1	Overdue	1
Person 2	Overdue	1
Person 2	Overdue	1
Person 2	Overdue	1
Person 3	Overdue	1
Person 3	Overdue	0
Person 3	On Time	0

The above is the table from which I am building my report from.
In my report I would like to show the following :

Name	Rank
Person 2	1
Person 1	2
Person 3	2

I have tried various different types of RANK CALCULATE formulas but cannot seem to get it to produce the numbers I want!

Tried RANKX with COUNT and RANKX with SUM. I am missing something.

 

[maabadi]

Use this method

Book1
	A	B	C	D	E	F	G	H
1	Name	Due Status	Active Overdue		Rank	Name	Countifs
2	Person 1	On Time	0		1	Person 2	3
3	Person 1	Overdue	1		2	Person 1	2
4	Person 1	Overdue	1		3	Person 3	2
5	Person 2	Overdue	1
6	Person 2	Overdue	1
7	Person 2	Overdue	1
8	Person 3	Overdue	1
9	Person 3	Overdue	0
10	Person 3	On Time	0
11
12
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=COUNTIFS($A$2:$A$10,$F$2:$F$4,$B$2:$B$10,"Overdue")

 

[JustynaMK]

Hi,

Before I start working with DAX, can you please explain how your ranking works? I.e. Why Person 1 and 3 have the same ranking (2)? Is it based on the count of "On Time" occurrences?

 

[_eNVy_]

Hi,

Before I start working with DAX, can you please explain how your ranking works? I.e. Why Person 1 and 3 have the same ranking (2)? Is it based on the count of "On Time" occurrences?

Name	Due Status	Active Overdue
Person 1	On Time	0
Person 1	Overdue	1
Person 1	Overdue	1
Person 2	Overdue	1
Person 2	Overdue	1
Person 2	Overdue	1
Person 3	Overdue	1
Person 3	Overdue	1
Person 3	On Time	0

So sorry! The Active Overdue looks for the word "Overdue" and issues a 1 if there is and 0 if anything else.

 

[JustynaMK]

No worries at all & thanks for clarifying

One solution might be to create a dynamic DAX table using the following formula:

tRank = 
    var temp = 
        SUMMARIZE(
            FILTER(tblOverdue, tblOverdue[Due Status] = "Overdue"), tblOverdue[Name], 
            "Sum Overdue", SUM(tblOverdue[Active Overdue]))
return
    ADDCOLUMNS(temp, "Rank", RANKX(temp, [Sum Overdue]))

You can then use this table on your visualization, e.g.:

Does that work for you? Or do you need to have a solution that is not a DAX table, i.e. DAX column / measure / Power Query?

 

[RoryA]

For a measure, I think you'd just need one measure that sums the ActiveOverdue field (let's call it NumOverdue) then add a new measure using that - something like:

RANKX(ALL(Table1[Name]),[NumOverdue])

 

[_eNVy_]

No worries at all & thanks for clarifying

One solution might be to create a dynamic DAX table using the following formula:

View attachment 32232

tRank =
    var temp =
        SUMMARIZE(
            FILTER(tblOverdue, tblOverdue[Due Status] = "Overdue"), tblOverdue[Name],
            "Sum Overdue", SUM(tblOverdue[Active Overdue]))
return
    ADDCOLUMNS(temp, "Rank", RANKX(temp, [Sum Overdue]))

View attachment 32233

You can then use this table on your visualization, e.g.:

View attachment 32234

Does that work for you? Or do you need to have a solution that is not a DAX table, i.e. DAX column / measure / Power Query?

Thank you for your swift reply!

I used your DAX formula and I had to change it slightly (I have a filter on the page which for some reason was not applying to the table) but it works!

Thank you very much!