Ranking is not showing correctly on card

[Vish24]

Hello All,

I have seen so many posts and tried their solutions but nothing is working out. Please help me with this ranking:

I have my Data for sles with names and year:

Names  Year  Sales

A          2019    222

A          2019    456  

A          2018   678 

B          2019   555 

B         2018  33 

C        2018    23

I have two slicers for Name and Year: I want to show th rank of selected name with selected year. If year is not selected then combine ranking of 2018+2019 sales. My ranking is coming fine in table but its coming wrong in card.

Please Help!

C

[v-gizhi-msft]

Hi,

Please try to add an index column in Query Editor.

And then create a measure to show the rank:

rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)

Add this measure into table visual, when you do not choose any slicer, it shows:

When you choose year and name, it shows:

Hope this helps.

Best Regards,

Giotto Zhi

[Vish24]

@v-gizhi-msft  Thank you! I will check this and confirm if its my solution.

[Vish24]

@v-gizhi-msft hello, did you try this ranking measure on card display. its not showing correct ranking in card if I am selecting a name from slicer. In table its coming fine.

[mwegener]

Hi @Vish24 ,

@v-gizhi-msft  solution looks correct.

PBIX

But what do you mean with this?

If name is not selected the show the rank of 2018+2019.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Vish24]

@mwegener sorry that was a mistake. I meant if year slicer is not selected then it should show rank of 2018+2019 sales.

[v-gizhi-msft]

Hi,

The measure in a Card visual can only show the single value.

According to your description that if name is not selected, then it should show the rank of 2018+2019.

But for each year, it has different ranks and can not be put in a Card visual as one single value.

Do you want to compare 2018+2019 total sales with other year's total sales such as 2017 or 2016?

Please give more info.

Best Regards,

Giotto Zhi

[Vish24]

@v-gizhi-msft sorry, that was a typing error. I meant if year is not selected and only name is selected then it will show ranking based on 2018+2019 sales.

[mwegener]

Hi @Vish24 ,

is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[v-gizhi-msft]

Hi,

Please try this rank measure:

rank = IF(SELECTEDVALUE('Table'[Year])<>BLANK(),RANKX(ALLSELECTED('Table'),CALCULATE(SUM('Table'[Sales])),,DESC,Dense),RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])),,DESC,Dense))

Best Regards,

Giotto Zhi

[mwegener · Accepted solution]

Hi @Vish24 

try this...

rank = RANKX(ALL('Table'[Name]),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)

... and put an advanced filter on the (card) visual "Table [Year] is not blank"

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support. 

[Vish24]

@v-gizhi-msft thank you for your response. But I want the final result for C with year 2019 to be rank 5 and sum of sales 37. its not ranking as per the total sales for each year

[v-gizhi-msft]

Hi,

Please try to change the original rank measure to this:

rank = RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Sales])),,DESC,Dense)

And it shows:

Best Regards,

Giotto Zhi

[amitchandak]

Refer to this: Here I have tried to show 1st, 2nd rank by brand. In case the year is filter this should work

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

Also refer :

https://community.powerbi.com/t5/Desktop/Rankx-by-Group-with-filter/m-p/922597#M442150

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Vish24]

@amitchandak  Thanks! I will go through this and let you know if I am able to do this.

[Vish24]

@amitchandak I couldn't see the attached button to attach my sample file. But here is my data. I am not sure if the blanks for year are giving any problem:

Name	Sales	Year
A	123	2018
A	23	2019
A	455
B	34	2018
B	345	2019
C	22	2018
C	24	2019
C	13	2019
C	12
D	45	2018
D	565	2019
A	344	2018
A	4444	2019
C	22	2018
B	134	2018
B	5674
A	364654	2018
E	2432	2018
E	657	2019
F	576
F	2343	2018

I have slicer for name and year. I need to show rank of selected year and name in a card. If name is not selected the show the rank of 2018+2019. As on my page I have filter to take only non blank year values: 

[Vish24]

@mwegener if you could help with this whenever you get time. I have tried many solutions but nothing is woring with card display. Rank is coming fine in table display but when I am selecting a name from slicer its not giving corrrect rank on card disply.

Re: Ranking is not showing correctly on card

I couldn't see the attached button to attach my sample file. But here is my data. I am not sure if the blanks for year are giving any problem:

Name	Sales	Year
A	123	2018
A	23	2019
A	455
B	34	2018
B	345	2019
C	22	2018
C	24	2019
C	13	2019
C	12
D	45	2018
D	565	2019
A	344	2018
A	4444	2019
C	22	2018
B	134	2018
B	5674
A	364654	2018
E	2432	2018
E	657	2019
F	576
F	2343	2018

I have slicer for name and year. I need to show rank of selected year and name in a card. If name is not selected the show the rank of 2018+2019. I don't need to include the sales in calculation for which year is blank.

Thank you!

[Vish24]

I couldn't see the attached button to attach my sample file. But here is my data. I am not sure if the blanks for year are giving any problem:

Name	Sales	Year
A	123	2018
A	23	2019
A	455
B	34	2018
B	345	2019
C	22	2018
C	24	2019
C	13	2019
C	12
D	45	2018
D	565	2019
A	344	2018
A	4444	2019
C	22	2018
B	134	2018
B	5674
A	364654	2018
E	2432	2018
E	657	2019
F	576
F	2343	2018

I have slicer for name and year. I need to show rank of selected year and name in a card. If name is not selected the show the rank of 2018+2019. I don't need to include the sales in calculation for which year is blank.

Thank you!

[tkirilov]

Hi @Vish24 ,

Perhaps that's because a card would only display first/last rank or count and a workaround to that would be to have a name selected by default in the name slicer, which users can then change to see the relevant rank for other names. I hope that helps. 

Best wishes,

T

[Vish24]

Thank you for your response. I tried but still its not showing correctlt for some.

[tkirilov]

That potentially means that there are some rankings missing or not calculated correctly, so I'd encourage you to review your rank calculation method. Perhaps you could share an image of that?