CODEWITHSUNDEEP
powerbidax
thne123
thne123

Reputation: 17

Power BI Rankx issue

I'm trying to create, what I thought was a relatively easy measure to rank sales (amount) per store. But I'm having an issue where stores can have the same rank, despite having huge differences in their sales value, and I'm not quite sure how to proceed.

Table A has a date column, a StoreID which I use to get the Store Name from another Table, Type column which I filter between, and then Amount. Small sample of data: There are millions of rows where there are multiple rows per day per store, per type with various Amount.

Date StoreID Type Amount Country
2024-07-09 ABC1 XY1 100 SWE
2024-07-09 ABC1 XY2 150 SWE
2024-07-09 ABC1 XY3 150 SWE
2024-07-09 ABC2 XY2 100 SWE
2024-07-09 ABC3 XY2 175 SWE
2024-07-09 ABC4 XY4 200 SWE

What I'm trying to create is a measure that ranks the Stores depending on their sum of Amount.

In my report I have 4 table visualizations, one for each country with all their respective Stores, their rank and the sum of the Amount. I also have a date slicer and a slicer for switching between the various Types we have. Single-select only.

However, the ranking like I mentioned gives me weird results where two (or even more) stores can have the same rank even though they have vastly different Amounts.

I've tried many iterations of my measure and even asked ChatGPT for help but didn't lead anywhere. Only variations of the same issue...

Current version that gives me the issue I described:

VAR _Country = SELECTEDVALUE(TableB[Country])
RETURN
 
  RANKX(
    FILTER(
    ALL(TableB),
    TableB[Country] = _Country),
    [Amount (EUR)],,DESC,Dense)

I'm not quite sure what do to here, I've tried expanding with filters and "all" for Type etc but still not quite right results...

Any suggestions?

Upvotes: 0

Views: 71

Answers (1)

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

The issue in your measure is you put ALL. This will concider all the columns in the table to do the ranking. Ranking criteria should be column level rather than table level.

VAR _Country =
    SELECTEDVALUE ( TableB[Country] )
RETURN
    RANKX (
        FILTER ( ALL ( TableB[Amount] ), TableB[Country] = _Country ),
        [Amount (EUR)],
        DESC,
        DENSE
    )

If you want a more dynamic ranking replace ALL with ALLSELECTED as below.

VAR _Country =
    SELECTEDVALUE ( TableB[Country] )
RETURN
    RANKX (
        FILTER ( ALLSELECTED ( TableB[Amount] ), TableB[Country] = _Country ),
        [Amount (EUR)],
        DESC,
        DENSE
    )

Upvotes: 0

Related Questions