Calculate percentage index of article price

[alan7lp]

Hello!

I am trying to calculate the INDEX % variation of same articles across different suppliers:

SUPPLIER	ITEM_NO	Current Price	MinPrice	INDEX
SUPPLIER A	1	€ 49.00	€ 49.00	100.00%
	2	€ 34.00	€ 32.00	106.25%
SUPPLIER B	1	€ 51.00	€ 49.00	104.08%
	2	€ 32.00	€ 32.00	100.00%
SUPPLIER C	1	€ 55.00	€ 49.00	112.24%
	2	€ 36.00	€ 32.00	112.50%

What I need to calculate is as following:

• Where article is the same across different suppliers, identify the LOWEST PRICE and allocate to it 100% index. In the example above for article 1 , lowest price is 49.00 which gives 100% index, corresponding to SUPPLIER A.
• Next, based on that 100% index (lowest price), the rest of suppliers with same article but more expensive price should have a calculation showing the percentage variation.  Example, SUPPLIER B article 1 price is 51.00 which represents an INDEX of 104.08%. For SUPPLIER C article 1 price is 55.00 which represents an INDEX of 112.24%
• For article 2 the logic is the same but SUPPLIER B has the lowest price in this case.
• On this example, my data is filtered for those 2 articles which are produced by these 3 suppliers. Last step of the calculation and if NOTHING is filtered therefore all articles and all suppliers will be displayed, the index should calculate the same logic based on article number. 

NOTE: current price column is coming from a measure that is filtering the original price which falls in a range of dates containing TODAY. This is because for 1 article I can have more than 1 value depending on dates (past and future). Therefore, I can't just do a MIN for the table colum containing the base price because the lowest price might not be the CURRENT lowest price.

I hope it's clear enough.

If more info o details are needed, please let me know. 
Regards,
Alan

[SivaMani]

@alan7lp, One clarification - You already have MinPrice column/measure and just need to calculate Index %?

[alan7lp]

Hello @SivaMani ,

I do not. That should be part of it. Get the minimum price of 1 particular article and "link it" to all suppliers having the same article and then from there calculate the index which will be 100 for the one with the lowest article price and then for the rest the corresponding % based on their original price vs the minimum one.

I hope that's clear!

Thanks for looking into it 🙂

[SivaMani · Accepted solution]

@alan7lp, Try the below measures,

Min_Price = MINX(ALL('Table (2)'[SUPPLIER]),[CurrentPrice])

Index % = DIVIDE([CurrentPrice],[Min_Price])