median filtered measure, counting 'empty records'

[blazko]

Hi, Id like to continue case from other topic 

median-of-calculated-median

the formula is:

Median Score = IF(
			ISFILTERED('Users'[user]),
			CALCULATE(MEDIAN('Users'[score])),
			MEDIANX('Sectors',CALCULATE(MEDIAN('Users'[score])))
		)

The solution works great but it seems to calculate through 'empty records'. If it calulates through a filterd list of users, but some user doesnt have scores in that calulated month (wasnt working that time), it adds a median=0. So the total median is calculated from ex not 4 numbers, but 7 (red marking below). it should give 51,38%, but gives 16,75% instead.

Tried to tweak with filters and so but nothing. Adding visual/page filter for measured median (I've inserted separate measure for
MEDIAN('Users'[score])
inside the code) ,for not blank, greater than 0, somehow messes it even more.

Its like its doing something "wrong" here:
MEDIANX('Sectors',CALCULATE(MEDIAN('Users'[score])))
How to exlude calculations that give here 0 as a result?

[v-ljerr-msft · Accepted solution]

Hi @blazko,

Could you try the formula below to see if it works in your scenario?

Median Score =
IF (
    ISFILTERED ( 'Users'[user] ),
    CALCULATE ( MEDIAN ( 'Users'[score] ) ),
    MEDIANX (
        FILTER ( 'Sectors', CALCULATE ( MEDIAN ( 'Users'[score] ) ) > 0 ),
        CALCULATE ( MEDIAN ( 'Users'[score] ) )
    )
)

Regards

[blazko]

OMG yes! Thank You!

[v-shex-msft]

Hi @blazko,

Can you please share the pbix file to test? I build a sample and test with your formula, it works on my side.

Regards,

Xiaoxin Sheng