skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Getting started
      • Overview
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • To **bleep** With MEDIAN

    To **bleep** With MEDIAN

    08-25-2020 05:53 AM - last edited 08-25-2020 08:20 AM

    Super User Greg_Deckler
    Super User
    1330 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    To **bleep** With MEDIAN

    ‎08-25-2020 05:53 AM

    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-erro... 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


    @ me in replies or I'll lose your thread!!!
    Instead of a Kudo, please vote for this idea
    Become an expert!: Enterprise DNA
    External Tools: MSHGQM
    YouTube Channel!: Microsoft Hates Greg
    Latest book!:
    Mastering Power BI 2nd Edition

    DAX is easy, CALCULATE makes DAX hard...
    ToHellWithMEDIAN.pbix
    Labels:
    • Labels:
    • Mathematical
    Message 1 of 2
    1,330 Views
    3
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    MFelix
    Super User MFelix
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-01-2020 05:15 AM

    @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.


    Regards

    Miguel Félix


    Did I answer your question? Mark my post as a solution!

    Proud to be a Super User!

    Check out my blog: Power BI em Português



    Message 2 of 2
    1,113 Views
    2
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices