Help with Rank in DAX

Hi javif_84 ,

To calculate the rank of "Company X" within each category based on sales (or another metric) in Power BI, you can create a DAX measure using the RANKX function. Here's the step-by-step process:

Formula for Ranking:

Company Rank = 
RANKX(
    FILTER(
        ALL('YourTable'[Company Name]), 
        'YourTable'[Category] = SELECTEDVALUE('YourTable'[Category])
    ),
    CALCULATE(SUM('YourTable'[Company Sales (000)])),
    ,
    DESC,
    SKIP
)

Explanation:

1. FILTER(ALL('YourTable'[Company Name]), ...): Ensures the ranking considers all companies within the same category as the current row.
2. CALCULATE(SUM('YourTable'[Company Sales (000)])): Calculates the total sales of each company to use as the ranking basis.
3. SELECTEDVALUE('YourTable'[Category]): Limits ranking to the current category in the matrix row.
4. DESC: Ranks in descending order (highest sales get rank 1).
5. SKIP: Handles tied ranks by skipping numbers instead of assigning duplicates.

Steps in Power BI:

1. Go to the Modeling tab and click New Measure.
2. Paste the formula above, replacing 'YourTable' with the name of your table and column names with the appropriate fields.
3. Add the new measure to your matrix in the Company Rank column.

This should display the rank of "Company X" for each category based on its sales. Let me know if you encounter any issues!

Please mark this as solution if it helps you. Appreciate Kudos.

Thanks for the detailed response!

Unfortunately it does not work... Though I do not understand well the logic 😞

I want to rank the company "Zoetis" independantly of any category in my dataset but in your dax in any moment I filter by Zoetis so I do not undertand how it can give me the rank of zoetis in these categories

Hi javif_84 
Thank you for reaching out microsoft fabric community forum.

Below is the  DAX to get the rank of the Company by its sales
Sales Rank =

RANKX(

    FILTER(

        ALL('Sheet1'[Company Sales]),  -- Removes filters on 'Company Sales' column

        NOT(ISBLANK('Sheet1'[Company Sales]))  -- Exclude blanks

    ),

    CALCULATE(

        IF(ISBLANK(MAX('Sheet1'[Company Sales])), 0, MAX('Sheet1'[Company Sales]))  -- Replace blank with 0 and aggregate the values

    ),

    ,

    DESC,  -- Rank in descending order

    Dense   -- Dense ranking (no gaps)

)

Please find the attached PBIX file for your reference.

Let me know if you need further assistance!

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

Thank you.

Hi javif_84 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.