Forum Discussion

Pelahibdo's avatar
Pelahibdo
Frequent Visitor
1 year ago

Incorrect result using Rankx for subcategories when adding new columns

Hi,

I am trying to rank by subcategory (product ID) in a table and it works fine using the following metric.

Rank = rankx(ALLSELECTED('Product'[Product ID]),calculate(sum('Sales'[Sale])))

But as soon as I add new columns such as Product Name, the ranking becomes 1 for all the rows. I need to add the extra columns (4-5 new columns that could be from different tables) to show more information in my visual but don't want them to affect the ranking. 

Here is my table:

Sales personProduct IDSalesRank
A11A5002
A21B10001
A31C15003
B41D20002
B51E25001
C61F30004
C71G35003
C81F40002
C945001

 

The sale value comes from Sales table which has a relationship with my product table using Product ID (there could be few rows per product ID, so I need to use sum([Sale]))

 

Thanks

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  Pelahibdo ,

     

    Please try this formula:

    Rank = VAR SalesPersonGroup = 
    FILTER(
        ALL('Table'),
        'Table'[Sales Person] = SELECTEDVALUE('Table'[Sales Person])
    )
RETURN
    RANKX(
        SalesPersonGroup,
        CALCULATE(SUM('Table'[Sales])),
        ,
        DESC,
        DENSE
    )

     

     

    I've made a test for your reference:

    1\Mya data table(Table)

    2\Add a measure

    Rank = VAR SalesPersonGroup = 
    FILTER(
        ALL('Table'),
        'Table'[Sales Person] = SELECTEDVALUE('Table'[Sales Person])
    )
RETURN
    RANKX(
        SalesPersonGroup,
        CALCULATE(SUM('Table'[Sales])),
        ,
        DESC,
        DENSE
    )

    3\Result

     

    Best Regards,

    Bof

     

     

    • Pelahibdo's avatar
      Pelahibdo
      Frequent Visitor
      1 year ago

      Thanks, but gives me the following error:

      Visual have exceed the available resources.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Pelahibdo ,

         

        Please review the attached .pbix file to confirm that it meets your requirements.

        Alternatively, you may consider providing your .pbix file (please ensure any private data is removed).

         

        Best Regards,

        Bof