Find Nth minimum and do some recalculations

[Michael_nik]

Hi guys!

I was given a task to make a test project for someone who doesn't work in PowerBI to do some simple calculations. So I used sample financial dataset from PowerBI library

And my task sounds like "For each Product find 5 th minimum price (Sale price column) and recalculate potential savings for the purchasing Client if this price was used for the cases when real price was above this 5 th min price. Visualize product corresponding savings in any appropriate way"

Hope anyone has any ideas how to do it...

Thanks in advance, Mike!

[Greg_Deckler]

@Michael_nik Should be able to use RANKX for this.

[Michael_nik]

Thank you for your reply!
But where exactle should I use RANKX? I mean - in a new column in main table or should I make a new table-dictionary where I can put all products and there 5th price?

[Greg_Deckler]

@Michael_nik It's hard to say exactly with the information provided. If the rank is static then you could do it in a DAX calculated column. If it needs to be dynamic, you can do it using ADDCOLUMNS in a measure.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Michael_nik]

Sorry for misleading you, I'm just new to Power BI and this forum, I know much more about python.

This is main table (sample dataset from Power BI library):

If I got you straight, I need to add column with DAX formula in it containing RANKX(...).
What I need is a table where there is product name and its 5th min price (like this):

Product ----- 5th price

Montana ----         10

VTT ---------          15

Amarilla ----           20

Numbers are random just for this example. Sorry that I had to insert table like this, because table icon in message doesnt work and html error appears.
So after I get this table, the only thing to do is to find the savings for the Client in the main table.

Thank you for your time, Mike