CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visuals
jaffar hamandi
jaffar hamandi

Reputation: 19

Trying to use a table in PowerBI to show ranking of customer performance, but using 2024 data to create the ranking, and still showing 2025 data

So for context, I'm trying to create a top 10 ranking, with the top 10 in a table, and then every other customer not in that top 10 grouped in Other and still in the table. Right now I have the ranking based off all time totals, but I need the ranking to just be off the 2024 sales numbers.

These are the formulas I have at the moment to

A table to start the grouping of top 10 and other:

Customer and Other = UNION(VALUES('SOURCE: Sage_Data'[SOURCE: Customer_Mapping.Reporting Customer]),ROW("Reporting Customer","OTHER"))

The ranking measure:

Customer Rank = 
IF(SELECTEDVALUE('Customer and Other'[SOURCE: Customer_Mapping.Reporting Customer]) = "OTHER", [TopX] + 1, RANKX(ALL('Customer and Other'[SOURCE: Customer_Mapping.Reporting Customer]),[Total SalesGBP]))

The grouping measure (TopX is just 10 so I can change how many I want to show):

Top Trading Customer and Other GBP Sales = 
if([Customer Rank] <= [TopX], [Total SalesGBP],

    if(SELECTEDVALUE('Customer and Other'[SOURCE: Customer_Mapping.Reporting Customer]) = "OTHER", sumx(filter(all('Customer and Other'[SOURCE: Customer_Mapping.Reporting Customer]), [Customer Rank] > [TopX]),  [Total SalesGBP])
    )
)

Anyone know of any way to have the ranking be conditional on year revenue, but still able to display other years? I've been trying to do it with trial an error, but I just end up removing other years eg 2025 revenue figures? The output I'm trying to achieve is:

Top 20 Customers

I don't want the ranking measure as a value since it would appear in every month and I'm just using it to filter anyway, but I want to see how the trend of how top 10 revenues changed over time.

I'd prefer to have the Others be at the bottom with the rest sorted by ranking, but I can't do that without the ranking as a value, so just filtering on ranking and then sorting by revenue, therefore Others at the top is a compromise. I might also have the top 10 and then others as separate tables, one over the other, but that's a formatting thing so less important right now. If you have any tips on that, it would also be helpful

Upvotes: 1

Views: 22

Answers (0)

Related Questions