CODEWITHSUNDEEP
powerbidaxrankingrank
Charles916
Charles916

Reputation: 55

How to ignore the 'over partition by ' setting for Power BI Rankx

I have a table, with 3 columns： Account Name, Account Type, and Revenue (summed up from revenue of user selected months in the month slicer). I would like to rank this table according to each account's total revenue summed up in selected user selected months, so that the account with the highest total revenue of these months would be rank 1 and so on, but the Rankx always rank the table by both Account Name and Account Type.

This is the measure I'm using: Revenue_rank = Rankx(Allselected(Account[AccountName], Account[Sum_Revenue], , desc,Dense) in which Sum_revenue=sum(Account[revenue])

So the raw table is like:

enter image description here

While I want the result to be:

enter image description here

And what power bi gives me is the following one, you can see Account 4's rank changed from 2 to 1 here, because it takes the Account type into calculation (and account 4 has highest revenue as retail account), but I only want to rank by account, instead of by account, account type:

enter image description here

I'm not very sure how to let power bi rank the revenue only by account, while keeping the account type (which one account has only one for all time periods) in the table. Thanks a lot!

Added 2023-08-08: I think the problem is in the Sum_revenue=sum(Account[revenue])where I might need to add calculate allexcept to limit the columns being selected?

Upvotes: 0

Views: 200

Answers (1)

Vio
Vio

Reputation: 11

You need to create a summarized table with unique combinations of "Account Name" and "Account Type" and calculates the total revenue for each combination. Then, you use the RANKX function to rank the summarized table based on the calculated "Sum_Revenue" while ignoring the filters from the "Account Type" column (thanks to the use of ALL function).

With this measure, the ranking will be based on the total revenue for each unique "Account Name," while keeping the "Account Type" unaffected by the ranking.

Revenue_rank = 
RANKX(
    ADDCOLUMNS(
        SUMMARIZE(Account, Account[Account Name], Account[Account Type]),
        "Sum_Revenue", CALCULATE(sum(Account[revenue]))
    ),
    [Sum_Revenue],
    ,
    DESC,
    Dense
)

Upvotes: 0

Related Questions