Pulling names associated with ranks in RankX into variables.

[Old]

Hello my friends. I am hoping this is an easy question, but I have been using this website daily for months to learn powerBI and have come to my first problem requiring a post. 

What I want to do is to create a measure to pull in the provider name that is associated with rank 1/2/3 from my rankx. 

The code I have currently does attach ranks properly to the provider names, and when I create a matrix visual and line it up everything is good. Unfortunately I need to be able to pull rank1 into a variable in my measure and rank2 and rank3, so that I can create a card visual that says "providername1 isranked #1 and providername2 is ranked #2 and providername3 is ranked #3" I'm going to pull in some numbers as well but that isnt giving me any trouble. These ranks will change monthly.

I'm not tied to any structure, so if I should be embedding everything into the rankX instead of making all these variables, or if there is a good way to loop it I'm all for any ideas you have. 

I have it working with "TopN" except only for #1, I do not know how to do anything like "IF TOPN = 2" so I am converting to rankX but I am willing to try anything.

This is my current code, you will notice it does nothing to pull providername into provider01 variable. I need it to.: 

Spoiler

CPD Provider 01 =

VAR providerRank =( RANKX ( ALLSELECTED( CPD_Table[PROVIDER_NAME] ), CPD_Table[CPD Amount],,,Dense ))

VAR provider01 = CALCULATE( FILTER ( CPD_Table, providerRank = 1 ))
VAR provider02 = CALCULATE( FILTER ( CPD_Table, providerRank = 2 ))

VAR cpdText =

( provider01& " had the most, with " & FORMAT(cpdAmt,"$#,##0""k"";($#,##0""k"")") & provider02 & " was in second, .....etc")

return cpdText

What I need is something to the effect of this:

VAR provider01 = CPD_Table[PROVIDER_NAME] THAT IS RANKED 1

VAR provider02 = CPD_Table[PROVIDER_NAME] THAT IS RANKED 2

Thank you all for any help, even if that is just telling me which board this should have been posted on. 

[v-janeyg-msft · Accepted solution]

Hi, @Old 

According to your needs, I made a rank column and a measure. The rank column serves the measure, and the measure is placed in the card visual to display the result. When the data is refreshed, it will be updated automatically.

Like this:

rank = RANKX (CPD_Table,[CPD Amount],,,Dense)

Measure =
VAR provider01 =
    CALCULATE ( MAX ( CPD_Table[PROVIDER_NAME] ), CPD_Table[rank] = 1 )
VAR provider02 =
    CALCULATE ( MAX ( CPD_Table[PROVIDER_NAME] ), CPD_Table[rank] = 2 )
VAR provideramt01 =
    CALCULATE ( MAX ( CPD_Table[CPD Amount] ), CPD_Table[rank] = 1 )
VAR provideramt02 =
    CALCULATE ( MAX ( CPD_Table[CPD Amount] ), CPD_Table[rank] = 2 )
RETURN
    provider01 & " had the most with "
        & FORMAT ( provideramt01, "$#,##0""k"";($#,##0""k"")" ) & ", " & provider02 & " was in second with "
        & FORMAT ( provideramt01, "$#,##0""k"";($#,##0""k"")" )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[Old]

Hello Janey!

Thank you so much this is nearly perfect. 

The only problem I am having now is the ranking, I appologize for not including this in the original and I will update it once we have this figured out so people can find this more easily.

This report I am building has a set of filters to change it on the fly. Specifically Market and Date are giving me a problem, In June provider a was #1 amount, in july provider B is #1 amount, the same thing happens in the different markets. 

That is why I was trying to use a measure but I have nothing against using a column it will support the needed filters. 

Thanks for any help you can give!

[v-janeyg-msft]

Hi, @Old 

Rank is a calculated column, which can be used to filter. I can also calculate the ranking of each month according to the different months, but I need some sample data, So we can help you soon. You should know that It is hard for me to imagine your market and date and connections out of thin air.

Best Regards

Janey Guo

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

[Jihwan_Kim]

Hi, @Old 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

All measures are written inside the sample pbix file.

https://www.dropbox.com/s/rfna013np7u6r17/old.pbix?dl=0 

Hi, My name is Jihwan Kim.

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Old]

Hey! thanks for the quick response, that looks like it should work too. Unfortuantely I cannot download anything from dropbox, is there any way you can post the code snipit in a spoiler or something here?

[Jihwan_Kim]

Hi, @Old 

Please check the below measures.

Those are all I wrote inside the sample pbix file.

Qty total =

SUM(Sales[Qty])

Rank by Qty =

IF (

ISFILTERED ( Sales[Customer] ),

RANKX ( ALLSELECTED ( Sales[Customer] ), [Qty total],, DESC )

)

Top 5 Customers =

VAR numberN = 5

RETURN

SUMX (

KEEPFILTERS ( TOPN ( numberN, ALL ( Sales[Customer] ), [Qty total], DESC ) ),

[Qty total]

)

Top 5 customers name in card visual =

VAR numberN = 5

VAR topNtable =

TOPN ( numberN, ALL ( Sales[Customer] ), [Qty total], DESC )

RETURN

CONCATENATEX ( topNtable, Sales[Customer], ", ", [Qty total], DESC )

Hi, My name is Jihwan Kim.

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Old]

Hi Jihwan, Thanks for pasting that in for me!

Unfortuantely I dont think that will give me a way to pull out individual ranks does it? I need to be able to manipulate the results of the rankings so that I can move them around in text sentances on a card.

[Jihwan_Kim]

Hi, @Old 

I am not sure if I understood your question correctly.

For example, do you want to show the name whose rank is 2 in card visualization?

Then, you can use the below measure. You can change the number if you want to show different rank's customer's name.

Rank 2 Customer name =
VAR ranking = 2
VAR newtable =
FILTER (
ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Customer] ), "@rank", [Rank by Qty] ),
[@rank] = ranking
)
RETURN
MAXX ( newtable, Sales[Customer] )

Hi, My name is Jihwan Kim.

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Old]

Hey Jihwan, thanks again! ....unfortunately....that is giving me the same results as I was getting before. 

Maybe the specific output will help? If I put rank=1 into your code, I get (blank), if I put rank=2 in, I get the correct #1, if I put rank =3 in, I get (blank).

[Jihwan_Kim]

Hi, @Old 

inside my sample measure (Rank 2 Customer name = ) there are two other measures related.

one is Rank by Qty, and the other is Qty Total.

I do not know how you wrote the other two measures, and how you related those with other tables.

Please share your sample pbix file's link, then I can try to have a look into it to come up with a solution.

Hi, My name is Jihwan Kim.

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/