Forum Discussion
Rank Function
Hi everyone,
I am trying to use the Rank function to then use as a filter on a multi-card to show Our Brands + the Top 3 by Volume Sales. How can I order it by Volume Sales and not the Size Name? When I remove the 'WeeklyData'[Size Name], ASC, the ranking changes to just 1. Also is there a way to remove any Size Name that has OurBrand = 1, so that 2 doesn't disappear from the ranking as seen in the screenshot?
Weekly Rank =
VAR SourceTable =
ADDCOLUMNS ( ALLSELECTED ( 'WeeklyData'[Geography],'WeeklyData'[Concat (groups)],'WeeklyData'[Product], 'WeeklyData'[Size Name], 'WeeklyData'[Concat (groups)] ), "@Amt", SUM('WeeklyData'[Volume Sales]) )
VAR Result =
RANK ( DENSE, SourceTable, ORDERBY ( [@Amt], DESC, 'WeeklyData'[Size Name], ASC ) )
RETURN
if(sum('WeeklyData'[OurBrand])=1,0, Result)
Thanks for the input data.
I want to help you but your description is too vague and jumbled.
Considering deleting this post and starting from scratch.You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Avoid describing the problem in multiple, fragmented and conflicting posts. Try describe the problem with input data, output example and the step-by-step process in one post.
7 Replies
I want to help you but your description is too vague and jumbled. Please write it again.
For example you mention "Our brands" but there is no brand data in your example.
You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy).
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. Go to effort of creating fictional data.
* Please click the thumbs up button for these helpful hints and tips. Thank you.
Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.
Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.My goal is to make a measure that ranks the Size Name by Volume Sales, filterable by Geography, Time, Concat Group. My goal is to use this measure in a multi-card visual to show Our Brand Size Names + the other Top 3 Size Names by Volume Sales.
Sample Data:
Geography Time Size Name Volume Sales Price per Unit Concat (groups) OurBrand Date TotalSizeSales Latest Week Store A Week Ending 07-13-25 Our Product A 64oz 51829 3.8166627756661*$ Juice 1 07-13-25 1709873.59 FALSE Store A Week Ending 07-20-25 Our Product A 64oz 56903.5 3.62501 Juice 1 07-20-25 1709873.59 TRUE Store A Week Ending 07-20-25 Our Product A 96oz 53373.75 4.851506 Juice 1 07-20-25 1460217.419 TRUE Store A Week Ending 07-13-25 Our Product A 96oz 57115.5 4.773362 Juice 1 07-13-25 1460217.419 FALSE Store A Week Ending 07-20-25 PL A 64 OZ 49322 2.819019 Juice 0 07-20-25 1333216.293 TRUE Store A Week Ending 07-13-25 PL A 64 OZ 51059 2.797007 Juice 0 07-13-25 1333216.293 FALSE Store A Week Ending 07-13-25 S A 64 OZ 29980.5 3.545167 Juice 0 07-13-25 888080.5089 FALSE Store A Week Ending 07-20-25 S A 64 OZ 27995.5 3.620584 Juice 0 07-20-25 888080.5089 TRUE Store A Week Ending 07-20-25 PL A 96 OZ 12795 3.743372 Juice 0 07-20-25 350656.3278 TRUE Store A Week Ending 07-13-25 PL A 96 OZ 12662.25 3.653616 Juice 0 07-13-25 350656.3278 FALSE Store A Week Ending 07-20-25 C F AL 48 OZ 12171.38 4.343198 Juice 0 07-20-25 294282.0495 TRUE Store A Week Ending 07-13-25 C F AL 48 OZ 11218.13 4.613484 Juice 0 07-13-25 294282.0495 FALSE Store A Week Ending 07-20-25 S A 96 OZ 3808.5 5.538405 Juice 0 07-20-25 182436.7589 TRUE Store A Week Ending 07-13-25 S A 96 OZ 3931.5 5.494836 Juice 0 07-13-25 182436.7589 FALSE Store A Week Ending 07-13-25 S A 58 OZ 2409.124 5.104584 Juice 0 07-13-25 75611.56717 FALSE Store A Week Ending 07-20-25 S A 58 OZ 2280.533 5.130653 Juice 0 07-20-25 75611.56717 TRUE Store A Week Ending 07-13-25 R A 2 OZ 2263.206 3.970062 Juice 0 07-13-25 48963.15768 FALSE Store A Week Ending 07-20-25 R A 2 OZ 2188.635 4.089286 Juice 0 07-20-25 48963.15768 TRUE Store A Week Ending 07-20-25 M A 28 OZ 1601.178 6.694758 Juice 0 07-20-25 44675.76272 TRUE Store A Week Ending 07-13-25 M A 28 OZ 1729.833 6.569135 Juice 0 07-13-25 44675.76272 FALSE Store A Week Ending 07-20-25 C F AL 72 OZ 802.6875 5.960498 Juice 0 07-20-25 21032.08821 TRUE Store A Week Ending 07-13-25 C F AL 72 OZ 799.875 5.962011 Juice 0 07-13-25 21032.08821 FALSE Store B Week Ending 07-13-25 Our Product A 64oz 62194.8 3.8166627756661*$ Juice 1 07-13-25 1709873.59 FALSE Store B Week Ending 07-20-25 Our Product A 64oz 48367.98 3.62501 Juice 1 07-20-25 1709873.59 TRUE Store B Week Ending 07-20-25 Our Product A 96oz 53373.75 4.851506 Juice 1 07-20-25 1460217.419 TRUE Store B Week Ending 07-13-25 Our Product A 96oz 68538.6 4.773362 Juice 1 07-13-25 1460217.419 FALSE Store B Week Ending 07-20-25 PL A 64 OZ 69050.8 2.819019 Juice 0 07-20-25 1333216.293 TRUE Store B Week Ending 07-13-25 PL A 64 OZ 54122.54 2.797007 Juice 0 07-13-25 1333216.293 FALSE Store B Week Ending 07-13-25 S A 64 OZ 28781.28 3.545167 Juice 0 07-13-25 888080.5089 FALSE Store B Week Ending 07-20-25 S A 64 OZ 23796.18 3.620584 Juice 0 07-20-25 888080.5089 TRUE Store B Week Ending 07-20-25 PL A 96 OZ 16633.5 3.743372 Juice 0 07-20-25 350656.3278 TRUE Store B Week Ending 07-13-25 PL A 96 OZ 20259.6 3.653616 Juice 0 07-13-25 350656.3278 FALSE Store B Week Ending 07-20-25 C F AL 48 OZ 13023.37 4.343198 Juice 0 07-20-25 294282.0495 TRUE Store B Week Ending 07-13-25 C F AL 48 OZ 10545.04 4.613484 Juice 0 07-13-25 294282.0495 FALSE Store B Week Ending 07-20-25 S A 96 OZ 2818.29 5.538405 Juice 0 07-20-25 182436.7589 TRUE Store B Week Ending 07-13-25 S A 96 OZ 3774.24 5.494836 Juice 0 07-13-25 182436.7589 FALSE Store B Week Ending 07-13-25 S A 58 OZ 2120.029 5.104584 Juice 0 07-13-25 75611.56717 FALSE Store B Week Ending 07-20-25 S A 58 OZ 4333.013 5.130653 Juice 0 07-20-25 75611.56717 TRUE Store B Week Ending 07-13-25 R A 2 OZ 1471.084 3.970062 Juice 0 07-13-25 48963.15768 FALSE Store B Week Ending 07-20-25 R A 2 OZ 2407.499 4.089286 Juice 0 07-20-25 48963.15768 TRUE Store B Week Ending 07-20-25 M A 28 OZ 2401.768 6.694758 Juice 0 07-20-25 44675.76272 TRUE Store B Week Ending 07-13-25 M A 28 OZ 2110.396 6.569135 Juice 0 07-13-25 44675.76272 FALSE Store B Week Ending 07-20-25 C F AL 72 OZ 794.6606 5.960498 Juice 0 07-20-25 21032.08821 TRUE Store B Week Ending 07-13-25 C F AL 72 OZ 1039.838 5.962011 Juice 0 07-13-25 21032.08821 FALSE Store C Week Ending 07-13-25 Our Product A 64oz 74633.76 3.8166627756661*$ Juice 1 07-13-25 1709873.59 FALSE Store C Week Ending 07-20-25 Our Product A 64oz 41112.78 3.62501 Juice 1 07-20-25 1709873.59 TRUE Store C Week Ending 07-20-25 Our Product A 96oz 53373.75 4.851506 Juice 1 07-20-25 1460217.419 TRUE Store C Week Ending 07-13-25 Our Product A 96oz 82246.32 4.773362 Juice 1 07-13-25 1460217.419 FALSE Store C Week Ending 07-20-25 PL A 64 OZ 96671.12 2.819019 Juice 0 07-20-25 1333216.293 TRUE Store C Week Ending 07-13-25 PL A 64 OZ 57369.89 2.797007 Juice 0 07-13-25 1333216.293 FALSE Store C Week Ending 07-13-25 S A 64 OZ 27630.03 3.545167 Juice 0 07-13-25 888080.5089 FALSE Store C Week Ending 07-20-25 S A 64 OZ 20226.75 3.620584 Juice 0 07-20-25 888080.5089 TRUE Store C Week Ending 07-20-25 PL A 96 OZ 21623.55 3.743372 Juice 0 07-20-25 350656.3278 TRUE Store C Week Ending 07-13-25 PL A 96 OZ 32415.36 3.653616 Juice 0 07-13-25 350656.3278 FALSE Store C Week Ending 07-20-25 C F AL 48 OZ 13935.01 4.343198 Juice 0 07-20-25 294282.0495 TRUE Store C Week Ending 07-13-25 C F AL 48 OZ 9912.335 4.613484 Juice 0 07-13-25 294282.0495 FALSE Store C Week Ending 07-20-25 S A 96 OZ 2085.535 5.538405 Juice 0 07-20-25 182436.7589 TRUE Store C Week Ending 07-13-25 S A 96 OZ 3623.27 5.494836 Juice 0 07-13-25 182436.7589 FALSE Store C Week Ending 07-13-25 S A 58 OZ 1865.626 5.104584 Juice 0 07-13-25 75611.56717 FALSE Store C Week Ending 07-20-25 S A 58 OZ 8232.725 5.130653 Juice 0 07-20-25 75611.56717 TRUE Store C Week Ending 07-13-25 R A 2 OZ 956.2047 3.970062 Juice 0 07-13-25 48963.15768 FALSE Store C Week Ending 07-20-25 R A 2 OZ 2648.249 4.089286 Juice 0 07-20-25 48963.15768 TRUE Store C Week Ending 07-20-25 M A 28 OZ 3602.651 6.694758 Juice 0 07-20-25 44675.76272 TRUE Store C Week Ending 07-13-25 M A 28 OZ 2574.683 6.569135 Juice 0 07-13-25 44675.76272 FALSE Store C Week Ending 07-20-25 C F AL 72 OZ 786.714 5.960498 Juice 0 07-20-25 21032.08821 TRUE Store C Week Ending 07-13-25 C F AL 72 OZ 1351.789 5.962011 Juice 0 07-13-25 21032.08821 FALSE Thanks for the input data.
I want to help you but your description is too vague and jumbled.
Considering deleting this post and starting from scratch.You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Avoid describing the problem in multiple, fragmented and conflicting posts. Try describe the problem with input data, output example and the step-by-step process in one post.- Use FILTER to exclude OurBrand = 1 from the ranking logic.
- Rank by Volume Sales only, using RANKX with DESC.
- Return BLANK() for OurBrand rows so they don’t affect the rank.
- Then filter visuals with: Weekly Rank <= 3 || OurBrand = 1 to show Top 3 + OurBrand.
Let me know if you want a dynamic Top N version or a disconnected slicer setup.Hi MichaelaMul
Thank you for reaching out to the Microsoft Fabric Forum Community.
Shahid12523 speedramps Thanks for the inputs.
I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.
Hi MichaelaMul
I wanted to check if you had the opportunity to review the information provided by user. Please feel free to contact us if you have any further questions.
Hi MichaelaMul
Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.
