cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISUGraber Member
Member

Dynamic Table Rank - how to get this to work?

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))

  

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic Table Rank - how to get this to work?

Hi @ISUGraber 

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.

5 REPLIES 5
v-cherch-msft Super Contributor
Super Contributor

Re: Dynamic Table Rank - how to get this to work?

Hi @ISUGraber 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ISUGraber Member
Member

Re: Dynamic Table Rank - how to get this to work?

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

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

 

 

FactSales

PRSN_Driver_Name__cClosedDateAgingScorecard Bucket
101/7/201926FILTER A
201/2/201914FILTER B
301/30/201923FILTER A
401/22/201915FILTER B
501/22/201911FILTER A
601/21/201910FILTER B
701/18/20191FILTER A
802/19/201914FILTER B
902/21/20190FILTER A
1003/5/20191FILTER B

 

 

wanted results:

PRSN_Driver_Name__cClosedDateAgingScorecard BucketRegionVP__cRankwhat I typicaly see today
902/21/20190FILTER AVP Marge11
701/18/20191FILTER AVP Someone Else2100
1003/5/20191FILTER BVP Bart2100
601/21/201910FILTER BVP Somebody41671
501/22/201911FILTER AVP Janet5etc…
201/2/201914FILTER BVP Bob6 
802/19/201914FILTER BVP Homer7 
401/22/201915FILTER BVP Jill8 
301/30/201923FILTER AVP Jim9 
101/7/201926FILTER AVP Bill10 
Super User
Super User

Re: Dynamic Table Rank - how to get this to work?

Hi @ISUGraber 

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.

ISUGraber Member
Member

Re: Dynamic Table Rank - how to get this to work?

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.

 

 

 

Super User
Super User

Re: Dynamic Table Rank - how to get this to work?

Hi @ISUGraber 

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 308 members 3,399 guests
Please welcome our newest community members: