create sum rank measure

[herbemischung]

Hello,

I am almost done with a report, but I have an Issue I can't figure out - and it makes me cracy so I hope I'll find help here:

There is a Table (Statistics) with the following columns: Customer / Revenue  /Date

I work with a few measures:

Revenue_actual_year= totalytd(sum(Statistics[Revenue]);Statistics[Date].[Date])

Revenue_past_year = CALCULATE([Revenue_actual_year];PREVIOUSYEAR(Statistics[Date].[Date]))

Delta_Revenue = Revenue_actual_year - Revenue_past_year

There is also a Report showing the Top 30 and Bottom 30 Customers in terms of Delta_Revenue.

But I am not able to create a Table that looks like this

Revenue (total) / Delta_Revenue (total)

Revenue (for all Customers within Delta_Revenue Top 30) / Delta_Revenue (for all Customers within Delta_Revenue Top 30)

Revenue (for all Customers within Delta_Revenue Bottom 30 / Delta Revenue (for all Customers within Delta_Revenue Bottom 30)

Revenue (Rest - neither Top 30 nor Bottom 30) / Delta Revenue (neither Top 30 nor Bottom 30)

I have an Excel - Sheet that does the trick, but its a little bit complicated and takes time, so I would prefer to have a power bi solution for this issue.

[v-yuta-msft]

Hi herbemischung,

As a general suggestion, you may create a rank column to achieve the ranking in every category using DAX formula below:

Rank by Category:
Rank = RANKX(FILTER(Table1, Table1[Category] = EARLIER(Table1[Category])), RANKX(ALL(Table1), Table1[Value]), , ASC, Dense)

Then you can create measure based on the result of rank.

Regards,

Jimmy Tao