Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
@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.
Hi @Vish24 ,
@v-gizhi-msft solution looks correct.
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.
@mwegener sorry that was a mistake. I meant if year slicer is not selected then it should show rank of 2018+2019 sales.
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
@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.
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.
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
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.
@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
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
Refer to this: Here I have tried to show 1st, 2nd rank by brand. In case the year is filter this should work
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
@amitchandak Thanks! I will go through this and let you know if I am able to do this.
@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:
@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.
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!
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
Thank you for your response. I tried but still its not showing correctlt for some.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |