Calculate price difference between 2 locations for a selected maturity - Need help please

[thomasAL]

Dear Community,

I am desperately trying to calculate the difference of price 'ProductValue' (from the Fact table) between 2 hub locations on a matrix visual. Basically I want to perform a A - B calculation with knowing that A is the based price (base in Germany "DE") and B represents a chosen hub location chosen from the '_dlv Zone-Hub' column in Dim Table. I want also to choose a specific maturity "Y+1-BL" or "Y+2-BL", etc, ... To picture the above described, here is what I am trying to operate:

I have tried to create a mnew measure with this DAX statement but it's not working: 

DIFF =

CALCULATE(

SUM('Fact TblMarketPrice'[ProductValue]),

FILTER('Dim TblMarketProduct','Dim TblMarketProduct'[_Mat T ST] = SELECTEDVALUE('Dim TblMarketProduct'[_Mat T ST])

),

FILTER('Dim TblMarketProduct','Dim TblMarketProduct'[_dlv Zone-Hub] = "DE")

) -

CALCULATE(

SUM('Fact TblMarketPrice'[ProductValue]),

FILTER('Dim TblMarketProduct','Dim TblMarketProduct'[_Mat T ST] = SELECTEDVALUE('Dim TblMarketProduct'[_Mat T ST])

),

FILTER('Dim TblMarketProduct','Dim TblMarketProduct'[_dlv Zone-Hub] = SELECTEDVALUE('Dim TblMarketProduct'[_dlv Zone-Hub]))

)

Can someone help me please? Thanks a lot!

Rgds,

Tom

[thomasAL]

What if I use one slicer only to choose the maturity "Y+1-BL" or "Y+2-BL" or"Y+3-BL" etc. The thing is that I know this is a row based calculation and it's very unconventional to computerize such logic. I can maybe create a new table and pivot it to obtain my hub locations values as columns, what do you think?

[v-jingzhang]

Hi @thomasAL has this problem been solved? Adding a new independent table for hub location and using it in the slicer should be a good idea. Or if it hasn't been solved, can you please share some sample data and the relationships between tables so that we can test it further? Thanks.

Best Regards,
Community Support Team _ Jing Zhang

[amitchandak]

@thomasAL , If you need two slicers, one of them needs to be an independent table. You need two tables one will not work. You need to use selectedvalues only for independent table

I have a blog, where I have did the same thing on dates. Refer if that can help

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601