Get Median for each row and subtract for each column

[Jake0001]

Dear all,

I would like to ask for assistance with an issue I am encountering on POWER BI, specifically with a particular DAX query. I aim to create a formula to calculate the median (not considering 0 or blank) for each product (MILK, BREAD.....) and then subtract it from the price for each country (ITALY JAPAN) to understand the deviation. 

The following one is the data set (in this case for example is characterized by one product but in reality, i have more products):

PRODUCT	LOCATION	PRICE
MILK	ITALY	6
MILK	USA	4
MILK	JAPAN	2
MILK	SPAIN	7
MILK	GERMAN	2

The result I would like to see is the following one (PRODUCT on row, LOCATION on column):

	ITALY		USA		JAPAN		SPAIN		GERMAN
	PRICE	DELTA	PRICE	DELTA	PRICE	DELTA	PRICE	DELTA	PRICE	DELTA
MILK	6	2	4	0	2	2	7	-3	2	2

MEDIAN: 4

DELTA= PRICE(EACH COUNTRY) - MEDIAN(EACH PRODUCT)

Is there any dax could help me to calculate the delta in this visualization? 

Thanks in advance!

[lbendlin · Accepted solution]

DELTA = sum('Table'[PRICE])-CALCULATE(MEDIANX('Table','Table'[PRICE]),REMOVEFILTERS('Table'[LOCATION]))

[Jake0001]

Thank you @lbendlin and @Daniel29195 ! Right now the dynamic median (excluding blank and 0) is implemented in my Bi report and the code is the following one (for who needs any helps):

DYNAMIC MEDIAN =

CALCULATE (

    MEDIANX(

        FILTER('table', 'table'[price] <> BLANK() && 'table'[price] <> 0),

        'table'[price]

    ),

    ALLSELECTED('table'[location])

)

 DELTA = sum('Table'[PRICE])-[DYNAMIC MEDIAN]

[lbendlin · Accepted solution]

DELTA = sum('Table'[PRICE])-CALCULATE(MEDIANX('Table','Table'[PRICE]),REMOVEFILTERS('Table'[LOCATION]))

[Jake0001]

Ibendlin, thanks for the advise. However, I would like to calculate a dynam median based on the slicer location in the visual. In this configuration, the slicer Location doesn't affect the median due to REMOVEFILTERS. In your opinion, is it possible to do something like this? 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Daniel29195 · Accepted solution]

@Jake0001 

instead of removefilters , use allselected('table'[location] ) 

hope this helps .

If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠

[lbendlin]

REMOVEFILTERS and ALLSELECTED are equivalent when applied to the same column.