Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, s...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

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.