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
    • QUARTILE

    QUARTILE

    05-03-2020 08:34 AM - last edited 05-16-2020 22:44 PM

    Super User Greg_Deckler
    Super User
    12637 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

    QUARTILE

    ‎05-03-2020 08:34 AM

    In my recent quest to create or catalog as many DAX equivalents for Excel functions, this was an interesting one because, well, frankly, I can't make heads or tails of how Excel is calculating the 1st and 3rd quartiles. Whatever method it is using does not seem to agree with any other method out there that I can find such as here or here or literally anywhere I have looked. No methods I have tried are able to replicate the numbers coming back from Excel's QUARTILE function. But, all other methods seem to agree on the 1st and 3rd quartile numbers for the set 1, 2, 4, 7, 8, 9, 10, 12. They all say that the 1st quartile is 3 and the 3rd quartile is 9.5. Excel gives 3.5 and 9.25 respectively. Note that QUARTILE.EXC gives 2.5 and 9.75 so that doesn't agree with anything either. So I guess, to *bleep* with Excel's QUARTILE function??

     

    Anyway, here is an DAX implementation of QUARTILE that agrees with every other method out there other than Excel...

     

    QUARTILE = 
        VAR __Values = SELECTCOLUMNS('Table',"Values",[Column1])
        VAR __Quart = MAX('Quartiles'[Quart])
        VAR __Median = MEDIANX(__Values,[Values])
        VAR __Quartile =
            SWITCH(__Quart,
                0,MINX(__Values,[Values]),
                2,__Median,
                4,MAXX(__Values,[Values]),
                1,MEDIANX(FILTER(__Values,[Values] < __Median),[Values]),
                3,MEDIANX(FILTER(__Values,[Values] > __Median),[Values])
            )
    RETURN
        __Quartile

     

    Perhaps someone from Microsoft's Excel team can mosey along and explain the formula behind Excel's quartile function? Has nobody ever noticed that it gives different results than literally every other quartile calculator/calculation?? Maybe all is a strong word. All of the ones I looked at, over a dozen.

     

    Something else that bugs me, all of the documentation on QUARTILE.INC, QUARTILE.EXC, PERCENTILE.INC, PERCENTILE.EXC all focus on the "inclusive/exclusive" part about the kth values from 0..1. Except that seems like the least important part to me because there are clearly different methods going on here in terms of how these functions compute the quartiles/percentiles because you can get very different answers, especially when dealing with even numbers of items. The fact that you can't use 0 and 1 in one of them seems like the last thing that you would want to explain but rather explain why the calculated values are different?

     

    And another thing with regard to the "interpolation", apparently that is why the numbers generated for the 1st and 3rd quartiles in Excel varies from the way everybody else does it so how exactly is this interpolation happening and why is it better or worse than the way everyone else seems to do it?

    eyJrIjoiNGY1MzNjNTYtNWQxMy00NmM0LThmMmUtZTIwNjg2YzhiZDcxIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ 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...
    Preview file
    846 KB
    QUARTILE.pbix
    Labels:
    • Labels:
    • Mathematical
    • Other
    Message 1 of 3
    12,637 Views
    3
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Hanson97
    Hanson97
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-03-2022 06:27 AM

    WOW!!!
    I was so confused with why finding Quartiles with PERCENTILEX.INC dont match with Box Plot visuals Quartiles
    But your solution fixed it..Thanks!!

    The only small fix required in your code is you will need to include the median if there are odd sets of data points and exclude them if there are even sets..

    QUARTILE = 
        VAR __Values = SELECTCOLUMNS('Table',"Values",[Column1])
        VAR _Count = COUNTROWS(_Values)
        VAR _reminder = MOD(_Count,2)
        VAR __Quart = MAX('Quartiles'[Quart])
        VAR __Median = MEDIANX(__Values,[Values])
        VAR __Quartile_Even =
            SWITCH(__Quart,
                0,MINX(__Values,[Values]),
                2,__Median,
                4,MAXX(__Values,[Values]),
                1,MEDIANX(FILTER(__Values,[Values] < __Median),[Values]),
                3,MEDIANX(FILTER(__Values,[Values] > __Median),[Values])
            )
    
        VAR __Quartile_Odd =
            SWITCH(__Quart,
                0,MINX(__Values,[Values]),
                2,__Median,
                4,MAXX(__Values,[Values]),
                1,MEDIANX(FILTER(__Values,[Values] <= __Median),[Values]),
                3,MEDIANX(FILTER(__Values,[Values] >= __Median),[Values])
            )
    RETURN
        IF( _reminder = 0,__Quartile_Even, __Quartile_Odd)
    Message 3 of 3
    3,481 Views
    0
    Reply
    wggomezvpalf
    wggomezvpalf
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-22-2022 11:48 AM

    I have a database with a lot of agents per day per skill, and for everydate everyone answer calls, one day one of them could receive different anount of calls in comparison to other dates, so I can create a table with Average of all calls, but when I try to find out Quartiles, for all agents in a determined period, the formula is not working

    someone can hell phere?

     

     

    Message 2 of 3
    3,675 Views
    0
    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