Dynamic Table Rank - how to get this to work?

[Anonymous]

I've been trying to use the rank function such as:  Rank with Visuals applied

However, when I apply this to my PBI my ranks go from 1 to 1700 or so (depending on what's in the table).  However, since there are only a few VPs I would like it to be 1,2,3,4.....  And ranked by VP.  My table that has VP's in it is DimPersonnel, which I'm not using below...

Below is my calculation:

Rank = RANKX(ALLSELECTED('FactSales') ,ROUND(CALCULATE(AVERAGE(FactSales[Aging]),
	DATESYTD('FactSales'[CaseCloseDate]),
	FILTER ( FactSales, AND ( FactSales[Scorecard Bucket] IN { "FILTERS"},  ( FactSales[OpenClosed] IN{"Closed" } )
	))),1))

[Mariusz]

Hi @Anonymous 

Make sure you use Dense as 4th argument in your RANKX expression

RANKX(,,,DENSE)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Mariusz ,

That almost works.  Now instead of into the thousands it's down to 20's.  Can you tell me if there's a way to have it just show rank on what is showing in the power bi visual?  So if only 3 VP's from above are in the visual I would see 1, 2, 3 even if #3 was the last one out of all of the VP's.

[Mariusz · Accepted solution]

Hi @Anonymous 

Try the below

Rank = 
VAR tbl = CALCULATETABLE(
    VALUES(FactSales[Aging]),
    ALLSELECTED()
)
RETURN 
RANKX(
    tbl, 
    ROUND(
        CALCULATE(
            AVERAGE(FactSales[Aging]),
	        DATESYTD(FactSales[ClosedDate]),
	        FILTER ( 
                FactSales, 
                AND ( 
                    FactSales[Scorecard Bucket] IN { "FILTERS"},  
                    FactSales[OpenClosed] IN {"Closed" } 
                )
            )
        ),
        1
    ),,,
    Dense
)

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-cherch-msft]

Hi @Anonymous 

Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

[Anonymous]

Thank you for the tip @v-cherch-msft 

Please find some dummy data below.  I hope this is enough, I trimmed it down a lot but these are the basics.  As you already probably know this table has a few thousand records in it. 

DimPersonnel

Id	VP
10	VP Bill
20	VP Bob
30	VP Jim
40	VP Jill
50	VP Janet
60	VP Somebody
70	VP Someone Else
80	VP Homer
90	VP Marge
100	VP Bart

FactSales

PRSN_Driver_Name__c	ClosedDate	Aging	Scorecard Bucket
10	1/7/2019	26	FILTER A
20	1/2/2019	14	FILTER B
30	1/30/2019	23	FILTER A
40	1/22/2019	15	FILTER B
50	1/22/2019	11	FILTER A
60	1/21/2019	10	FILTER B
70	1/18/2019	1	FILTER A
80	2/19/2019	14	FILTER B
90	2/21/2019	0	FILTER A
100	3/5/2019	1	FILTER B

wanted results:

PRSN_Driver_Name__c	ClosedDate	Aging	Scorecard Bucket	RegionVP__c	Rank	what I typicaly see today
90	2/21/2019	0	FILTER A	VP Marge	1	1
70	1/18/2019	1	FILTER A	VP Someone Else	2	100
100	3/5/2019	1	FILTER B	VP Bart	2	100
60	1/21/2019	10	FILTER B	VP Somebody	4	1671
50	1/22/2019	11	FILTER A	VP Janet	5	etc…
20	1/2/2019	14	FILTER B	VP Bob	6
80	2/19/2019	14	FILTER B	VP Homer	7
40	1/22/2019	15	FILTER B	VP Jill	8
30	1/30/2019	23	FILTER A	VP Jim	9
10	1/7/2019	26	FILTER A	VP Bill	10