Price Index Calculation

[RenatoFerrari]

Hi all! I'm trying to calculate price index based on two brands prices, for example, product1 is 90, product2 is 100, so the price index result will be 90% (90/100). The problem I'm facing in Power BI is that I don't know how to calculate it correctly and how to bring it in a friendly visual....

In Excel, I basically have a sheet with a list containing 'price battles' saying 'product1 x product2' is a battle, which have to bring 'product1' price and divide by 'product2' price, and its target is to be bellow 95%, so the price in the example is correct (90%), and with simple formulas and pivot tables I can bring it easily. I've tried to calculate it in Power BI using DIVIDE and CALCULATE, but i don't know how to use variable filters for each 'compare battle' (a table, for example), because I have a large list of price index battles to analyse. I've tried to create a table bringind price of the product1, price of the product2, and divide it, but it didn't work (maybe I did it wrongly).

I'm sending an excel file with an example to be more clear, so if some one could help me, I'll be grateful (I've tried to find solutions in youtube and also here in the forum, but without success) .

Thanks!

[tamerj1]

Hi @RenatoFerrari 

You can create two disconnected tables one for the reference product and one for the product to compare. You can load manually or create using power query or dax. 

the Price Index measure would be

Price Index =
DIVIDE ( SUM ( 'Table'[Value] ), SUM ( 'Table'[Unit] ) )

the Target % Measure would be

Target % =
AVERAGEX (
CROSSJOIN (
VALUES ( Reference[Reference Product] ),
VALUES ( Compare[Product to Compare] )
),
VAR Ref = Reference[Reference Product]
VAR Comp = Compare[Product to Compare]
RETURN
DIVIDE (
CALCULATE ( [Price Index], 'Table'[Product] = Ref ),
CALCULATE ( [Price Index], 'Table'[Product] = Comp )
)
)

place this measure in the values of a matrix visual then place month in the columns of the matrix then place both Reference[Reference Product] and Compare[Product to Compare] in the rows of the matrix. Note: you need to disable stepped layout in order to see both columns at the same level. 
It is also advised to have a slicer of both columns to control which products to view in the matrix and to limit the time consumed to perform the calculation to only the products of interest. 
also advised to disable the total as it does not have a real meaning othe otherwise add an ISINSCOPE or HASONEVALUE statement to the dax to limit the calculation. 

[RenatoFerrari]

https://1drv.ms/x/s!As41cnaF6uYWgZ1hPsm7TcnrWYaryw?e=X2X424 > excel file