Help with Rank in DAX

[javif_84]

Hi all

Hope you´re well!

I ve got an issue with a DAX report.

I created this matrix for POwer BI where I select the categories with the total Sales (column B) and the sales of the company (column D).

Now I want to understand the formula in DAX to get the rank of the Company X included in the dimension "Company Name" within the category 1,2 and etc where my company has sales.

Could you help me out please?

Thanks

Javier

[v-shamiliv · Accepted solution]

Hi @javif_84 
We are happy to hear that your issue has been resolved. Please mark the helpful reply and accept it as the solution. This will assist other community members with similar problems in resolving them more quickly.

Thank you.

[v-shamiliv]

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.

[javif_84]

Hi Sorry, let me check for this and I´ll get back asap 🙂

Best

Javier

[v-shamiliv]

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.

[javif_84]

Hi 

It does not work... 😞 Let me share a sample with the link if it helps, it is a sample with dummy data but same structure

Ok let me share a sample file https://we.tl/t-Cy0nZfPcqH

What I d like is to rank among all categories selected the Company A (just the company A) in the sample

See my real, so i have this table where I built all my metrics for total market and for my company, I selected the fields ZTS TC-L0 and ZTS TC-L1 and I want to have independently of what category/subcategory (ZTC TC L0 and ZTS TC L1) the rank of the company selected 

Hope this helps

And thanks so much for the support

best

Javier

[v-shamiliv]

Hi @javif_84 
Based on the provided sample, I have created a calculated column for the rank of Company A on estimated sales. Please review the attached .pbix file for further details.

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.

[javif_84]

mmm still does not work see solution provided in another forum... Thanks for everything 🙂

[javif_84]

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

[FarhanJeelani]

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.