Incorrect result using Rankx for subcategories when adding new columns

[Pelahibdo]

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 person	Product ID	Sales	Rank
A	11A	500	2
A	21B	1000	1
A	31C	1500	3
B	41D	2000	2
B	51E	2500	1
C	61F	3000	4
C	71G	3500	3
C	81F	4000	2
C	9	4500	1

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

[Anonymous]

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]

Thanks, but gives me the following error:

Visual have exceed the available resources.

[Anonymous]

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

[lbendlin]

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-...

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/1447...