Using result of MEDIANX in another measure

[slackerhx]

Hi, 

I'm trying to use the result of my first calculated measure in a second measure. 

1st Measure:

Median Rent = 

CALCULATE(MEDIANX('Table 1',[Rent PSM (Selected Currency)]),all('Table 1'[Client Name]),'Table 1'[Total Rent (USD)]<>BLANK())

where [Rent PSM (Selected Currency)] is a calculated measure that allows the results to be displayed based on the slicer for currency type eg. USD or EUR. 

2nd Measure: 

Difference = [Rent PSM (Selected Currency)] - [Median Rent]

The result (whenever recalculated) of the 1st measure is correct when i applied slicers such as City or Client Name to the card visual.

However, the result of 1st measure when placed in a table visual becomes incorrect as it recalculates based on individual address entries, which is more granular than city and client name.

I've attempted to use MEDIAN instead. Unfortunately, it wasn't a success as [Rent PSM (Selected Currency)] is a measure which the function doesn't accept. Perhaps, someone could suggest ways to incorporate a measure into the function?

Example: 

Card Visual = 28

Table Visual: 

Client Name	City	Address	Rent PSM (Selected Currency)	Median Rent	Difference
ABC	London	3 Baker Street	25	25	0
XYZ	London	26 Oxford Street	40	40	0
CDG	London	9 Wellington Street	28	28	0

The ideal outputs should be as follow: 

Client Name	City	Address	Rent PSM (Selected Currency)	Median Rent	Difference
ABC	London	3 Baker Street	25	28	-3
XYZ	London	26 Oxford Street	40	28	12
CDG	London	9 Wellington Street	28	28	0

Thus, greatly appreciate if anyone can suggest solutions to achieve the above desired results. 

Thank you. 

[AlB]

Hi @slackerhx

Have you tried to add an ALL( ) to get rid of the filter on the address in the table visual? Which filters do you want to keep when calculating the median, only the one on City? 

Median Rent=
CALCULATE (
    MEDIANX ( 'Table 1', [Rent PSM (Selected Currency)] ),
    ALL ( 'Table 1'[Client Name], 'Table 1'[Address]  ),
    'Table 1'[Total Rent (USD)] <> BLANK ()
)

[slackerhx]

Hi @AlB,  

I've tried using all() on the address column but it still didn't give the desired result. 

I want to keep the filters for [Client Name] and [City] to recalculate the median. 

[v-cherch-msft]

Hi @slackerhx

You may refer to below measure. If it is not your case, could you share the .pbix file so that we could help further on it?You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Median Rent =
CALCULATE (
    MEDIANX ( ALL ( 'Table 1' ), 'Table 1'[Rent PSM (Selected Currency)] ),
    'Table 1'[Total Rent (USD)] <> BLANK ()
)

Regards,

Cherie

[slackerhx]

@v-cherch-msft Hi Cherie, 

I've tried your method, though it managed to make median constant, however, the median is not responsive to slicers for client name and city. 

I've done up a sample file to illustrate the issue. Please see attached link.

https://1drv.ms/u/s!AoVBzNM7bDanhH2XtlmnB_-zBsVR

Thank you!

[v-cherch-msft · Accepted solution]

Hi @slackerhx

You may try below measure:

Measure =
CALCULATE (
    MEDIANX ( ALLSELECTED ( Table1 ), [Rent PSM (Selected Currency)] ),
    Table1[Total Rent (USD)] <> BLANK ()
)

Regards,

Cherie