To bleep With MEDIAN

Greg_Deckler — Tue, 25 Aug 2020 15:20:38 GMT

Here is an interesting one brought to us by RiskyBiscuts. Basically, you can't use MEDIAN to create columns. Why you may ask? Well, as elegantly explained by marcorusso , MEDIAN returns a Variant data type (as opposed to a decimal number according to the official Microsoft documentation). See the original thread here: https://community.powerbi.com/t5/Power-Query/quot-Expressions-that-yield-variant-data-type-quot-error-when/td-p/1320838/highlight/false. Hence why you get the error "Expressions that yield variant data-type cannot be used to define calculated columns". Which then leads to the mystery of why in the world are you hyphenating data type? I mean, I'm a consultant and we consultants over-hyphenate everything, but even I do not hyphenate data type. And another thing, shouldn't it be "Expressions that yield a variant..." Come on people, grammar! But, anyway, I digress.

Oh, and this also goes for MEDIANX, PERCENTILE.EXC, PERCENTILE.INC, PERCENTILEX.EXC and PERCENTILEX.INC. All useless for columns.

Why does MEDIAN return a variant? Is Microsoft intentionally trying to make us cry tears of sadness and despair? It's probably because you incurred bad karma from lying to your Power BI data model. I warned you that would happen. Or, could it be because you can do a median of strings, booleans and dates perhaps? Wouldn't that be cool if median supported...oh wait, nope, fail. Median doesn't support anything other than numbers apparently so that's not it. Lame.

Luckily though, if we remember our maths, we can brute force our way to victory to create a median in a column:

Median 1 = VAR __Table = ADDCOLUMNS( ADDCOLUMNS( 'Table', "Above",COUNTROWS(FILTER('Table',[Unique Deviation]<EARLIER([Unique Deviation]))), "Below",COUNTROWS(FILTER('Table',[Unique Deviation]>EARLIER([Unique Deviation]))) ), "Diff",ABS([Above]-[Below]) ) VAR __Min = MINX(__Table,[Diff]) RETURN MAXX(FILTER(__Table,[Diff]=__Min),[Unique Deviation])

Or you could use marcorusso's a bit more elegant solution that still uses MEDIAN:

Median 2 = CONVERT(MEDIAN('Table'[Unique Deviation]),INTEGER)

eyJrIjoiODY2OWFkY2QtMjUwMS00MTllLWIxNTAtMjU4ZGViNWIzYWQ2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: To bleep With MEDIAN

MFelix — Tue, 01 Sep 2020 12:15:28 GMT

Greg_Deckler ,

Why should we simplify things just by using CONVERT, we can create columns within colunmns and in tables so everything get's really fun.

Just kidding with you, sometimes we need to deconstruct things and return to our basic math thinking that way we can really understand what the functions are doing.

Very good post once more.

topic To **bleep** With MEDIAN in Quick Measures Gallery

To **bleep** With MEDIAN

Re: To **bleep** With MEDIAN

topic To bleep With MEDIAN in Quick Measures Gallery

To bleep With MEDIAN

Re: To bleep With MEDIAN