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.
I have a measure that gives the rank of a store, based on its sales against other stores.
I want to just display two pieces of info, the currently selected stores rank & the total number of stores.
I can display the rank in a table with all stores showing, but if i try to do it in a card, or just selecting my single store, i get a rank of 1.
Is it even possible to display a specific rank on a card?
Currently the only thing i can think of is storing the rank in a calc column but this is just not dynamic enough.
Solved! Go to Solution.
Hi @wilson_smyth,
I don't know how you are calculating your ranking but I ussually use one specific column to rank the values that I need to filter/slice them and them I can use it to have my ranking.
I have created a simple table with Store and Sales then added two measures:
NORMAL_RANK = RANKX( ALLSELECTED(Sales[Store]); CALCULATE( SUM(Sales[Sales]) ) )
This first measure interacts with the slicers/filter I introduce in the report so it's dinamic and my rank will be influenced by taking out stores from my visual or adding them
OVERALL_RANK = RANKX( ALL(Sales[Store]); CALCULATE( SUM(Sales[Sales]) ) )
In this measure using the ALL function no matter how I have my data sliced it will give me the complete ranking of the store so you can use it in your cards see print below. However using this one if you select more than one value in your slicer in your cards it will give you the first value so be aware of that limitation.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsApologies, should have responded sooner.
Yes, I found a solution, its pretty much what @MFelix suggested.
I was trying to use one calcuation to do two things:
- show the rankof all stores
- show just the rank of the selected score on a card.
The problem was that if i got a calculation to show the list rank, it always displayed a value of 1 when placed on a card, and if i got the correct calculation to show the rank on a card, it wouldnt show the ranked list. so 2 calculations were created.
Store Ranking Table = RANKX(ALL('Table'[Store]), CALCULATE([Score]),,0,Dense)
Individual Store Rank = RANKX(ALL('Table'[Store]), CALCULATE([Score], ALLSELECTED('Table'[Store])),,0,Dense)
Hi @wilson_smyth,
Have you resolved your problem? Was @MFelix's suggestion helpful to your scenario? If yes, please kindly mark the corresponding reply as an answer so that some more people having similar concern can find the solution more easily. If you still have any question, please feel free to ask.
Best regards,
Yuliana Gu
Apologies, should have responded sooner.
Yes, I found a solution, its pretty much what @MFelix suggested.
I was trying to use one calcuation to do two things:
- show the rankof all stores
- show just the rank of the selected score on a card.
The problem was that if i got a calculation to show the list rank, it always displayed a value of 1 when placed on a card, and if i got the correct calculation to show the rank on a card, it wouldnt show the ranked list. so 2 calculations were created.
Store Ranking Table = RANKX(ALL('Table'[Store]), CALCULATE([Score]),,0,Dense)
Individual Store Rank = RANKX(ALL('Table'[Store]), CALCULATE([Score], ALLSELECTED('Table'[Store])),,0,Dense)
@wilson_smyth Hello, Are you able to solve this problem. I am having the same issue. My ranking is coming fine in table but in card its coming wrong. I tried these solutions:
Store Ranking Table = RANKX(ALL('Table'[Store]), CALCULATE([Score]),,0,Dense)
Individual Store Rank = RANKX(ALL('Table'[Store]), CALCULATE([Score], ALLSELECTED('Table'[Store])),,0,Dense)
but Individual Store Rank is giving 1 and 2 only in card. I have names and sales. i have ultiple sales for a name: So I am creating slicer for name and year and showing ranking on card.
Hi @Vish24 ,
can you share some sample data and expected result?
If you are abble to send out a mockup file it would be very helpfull to guide you on the correct path.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsQuick update - Although the solution worked if using a table and a card, if i try to use either rank measure as the circle size on a , map, they all come out as 1.
I think theres some filter context im not understanding, but would appreciate any advice.
Update 2 - got this sorted, had to add ALL(Location[Latitude), ALL(Location[Longitude]) to the calculate filter, so now it doesnt take lat and long into consideration when ranking.
Next step, try and make it so the top ranking have the loweest number (1,2,3), but the biggest circle!
Hi @wilson_smyth,
I don't know how you are calculating your ranking but I ussually use one specific column to rank the values that I need to filter/slice them and them I can use it to have my ranking.
I have created a simple table with Store and Sales then added two measures:
NORMAL_RANK = RANKX( ALLSELECTED(Sales[Store]); CALCULATE( SUM(Sales[Sales]) ) )
This first measure interacts with the slicers/filter I introduce in the report so it's dinamic and my rank will be influenced by taking out stores from my visual or adding them
OVERALL_RANK = RANKX( ALL(Sales[Store]); CALCULATE( SUM(Sales[Sales]) ) )
In this measure using the ALL function no matter how I have my data sliced it will give me the complete ranking of the store so you can use it in your cards see print below. However using this one if you select more than one value in your slicer in your cards it will give you the first value so be aware of that limitation.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hi Mfelix,
Thanks in advance. Having issue with ranking ranking starting with 2 after adding row (region). i tried adding isinscope in the formula but ranking is still wrong. thanks
output: ranking of market selected inside a card in nation and northeast
example NY rank 8 in nation & in NE rank in 5
MARKET | Sum of 7Days_Rolling_sale_Per_Market | REGION |
Las Vegas | 0.3855 | WEST |
Albuquerque | 0.3692 | WEST |
Hawaii | 0.3565 | WEST |
San Francisco | 0.3452 | WEST |
San Diego | 0.3434 | WEST |
Los Angeles | 0.3341 | WEST |
LA North | 0.3305 | WEST |
Southern California | 0.3268 | WEST |
Long Island | 0.2753 | NORTHEAST |
Orlando | 0.2701 | SOUTH |
Salt Lake City | 0.2629 | WEST |
Washington DC | 0.2627 | NORTHEAST |
Sacramento | 0.2614 | WEST |
New York | 0.2575 | NORTHEAST |
Upstate NY | 0.2453 | NORTHEAST |
New Jersey | 0.2407 | NORTHEAST |
Miami | 0.239 | SOUTH |
New England | 0.238 | NORTHEAST |
Philadelphia | 0.2358 | NORTHEAST |
Virginia | 0.2356 | NORTHEAST |
Connecticut | 0.2329 | NORTHEAST |
Jacksonville | 0.2176 | SOUTH |
Tampa | 0.2132 | SOUTH |
South Carolina | 0.2104 | NORTHEAST |
North Carolina | 0.2069 | NORTHEAST |
Central PA | 0.1933 | NORTHEAST |
Hi @Anonymous ,
The 4rd parameter of the RANKX is the order you just need to do your measure like this:
RANKX(ALL(DimProvider),[ActualValue_CRM],, ASC )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks , The Rank start with 2, and i don't want to use allselected because i will use card visual, any solution?
much appreciated
Hi @Anonymous
Can you please give a little bit more insight on the expected result.Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCan't share.
i have tried to set not blank in filter for values. but ranking still starting from 2. just for ASC
Hi @Anonymous ,
If you don't use the ALLSELECTED or ALL syntax you won't be abble to show the specific value on card since there is no context to give you the correct resultl.
Another option can be using a countrows but once again I need some mockup data to help you better.
Are you abble to make a mockup of your data? Once again you can share it trough private messsage here on the forum.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |