Multiplying max rank (Highest Number) in one column to a static number

[Anonymous]

Hello everyone, 

I am new to Power BI from Excel and can't seem to find a solution to the following. 

Here is what I need to do and need your help

1. I have sales data in one column and want to rank them smallest to largest (or otherway round) in a seperate column. 

2. Once ranked I want to Identify the Highest rank and Multiply it with a static number like 0.15 in a seperate column. That is if the highest rank number is 235, I want to =235*0.15.

The goal is to have a dynamic calculation so that when the sales data is changed, the then largest rank is selected and multiplied with the fixed number 0.15.

Any help will be highly appreciated.

M.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You can try my way to achieve your goal by M query as well.

My Sample:

Open your Power Query Editor:

Group by all rows by Sales:

Sort Sales by ascending or descending, here i use descending.

Then add an index column and rename it as Rank.

Remove Sales column and expand ALL.

Finally add a Custom column.

If I add Customer H, Sales = 100 in my data source(Excel), and refresh the report.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[harshnathani]

HI @Anonymous ,

Create 2 Calculated columns

Rank Sales = RANKX('Table1',Table1[Sales],,DESC,Dense)

Static Multiply = IF (Table1[Rank Sales] = 1, Table1[Sales]*0.15)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)