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
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • 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
    • 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
    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
    • Multi-Column Aggregations (MC Aggregations)

    Multi-Column Aggregations (MC Aggregations)

    04-07-2018 15:53 PM - last edited 06-18-2018 13:44 PM

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

    Multi-Column Aggregations (MC Aggregations)

    ‎04-07-2018 03:53 PM

     

    "I like big aggregations and I cannot lie

    You other PBI'ers can't deny"

     

    The attached PBIX file contains a slew of aggregations that span multiple columns All standard quick aggregations are included, namely:

    • Max
    • Min
    • Sum
    • Average
    • Count
    • Count (Distinct)
    • Variance
    • Standard Deviation
    • Median

     

    This techinque can be employed with as many columns as required although I use 4 in the examples. Below is the "MC Max" measure for reference. The rest are a variation on this theme:

     

     

    MC Max = 
    VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Value1])
    VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Value2])
    VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Value3])
    VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Value4])
    VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
    VAR tmpValue = MAXX(tmpTable,[Column])
    RETURN tmpValue

     

     

     

     

     

    eyJrIjoiMTYyNTBhMWEtMzliZC00NGZjLWFkNjctN2YwYTIwMDMwMjYzIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    @ 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...
    MultiColumnAggregations.pbix
    132 KB
    Labels:
    • Labels:
    • Mathematical
    • Totals
    Message 1 of 10
    11,644 Views
    5
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    gsksarepta
    gsksarepta
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-21-2022 06:52 AM

    Hello, Thank you @Greg_Deckler @EtnaEstrella @dmalque for this multicolumn aggregations. Can I know how to get the column name of where this aggregation came from ?  I am using this to finding a limiting material that is low in supply. I found the minimum value with the formula and I need to get the name of that column. Can anyone help ? Thank you. 

    Message 9 of 10
    432 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to gsksarepta
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-21-2022 11:40 AM

    @gsksarepta Well, you could check for the existance of that value in the temp tables like:

    MC Min = 
    VAR tmpCol1 = SELECTCOLUMNS(Data,"Column",[Value1])
    VAR tmpCol2 = SELECTCOLUMNS(Data,"Column",[Value2])
    VAR tmpCol3 = SELECTCOLUMNS(Data,"Column",[Value3])
    VAR tmpCol4 = SELECTCOLUMNS(Data,"Column",[Value4])
    VAR tmpTable = UNION(UNION(UNION(tmpCol1,tmpCol2),tmpCol3),tmpCol4)
    VAR tmpValue = MINX(tmpTable,[Column])
    VAR __Result = 
      SWITCH(TRUE(),
        tmpValue IN __tmpCol1, "Value1",
        tmpValue IN __tmpCol2, "Value2",
        tmpValue IN __tmpCol3, "Value3",
        "Value4"
      )
    RETURN __Result

    @ 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...
    Message 10 of 10
    422 Views
    1
    Reply
    EtnaEstrella
    EtnaEstrella
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-24-2022 09:45 PM

    hola @Greg_Deckler estoy iniciando y probe esto, pero en el resultado de las columnas me lanza el mismo valor pra todos y no es el verdadero promedio y el valor maximo de cada Fila, me puedes ayudar?

    EtnaEstrella_1-1643089371244.png

    EtnaEstrella_2-1643089502763.pngEtnaEstrella_3-1643089526541.png

     

     

     

    Message 7 of 10
    3,829 Views
    0
    Reply
    EtnaEstrella
    EtnaEstrella
    New Member
    In response to EtnaEstrella
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎01-24-2022 09:58 PM

    lo resolví, cuando usas el codigo en aumentar columna sale el mismo valor, pero cuado añades una medida en el informe los valores ya salen correcto

    EtnaEstrella_4-1643090271561.png

     

    Message 8 of 10
    3,828 Views
    0
    Reply
    dmalque
    dmalque
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-22-2021 08:58 AM

    como podria omitir los ceros al calcular el promedio

    Message 6 of 10
    4,453 Views
    0
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-01-2019 09:23 PM

    Can this max formula be used with dates?

     

    I have dates in multiple columns, some are blank or missing, but need the max date per row across multiple columns. 

    Message 4 of 10
    9,415 Views
    0
    Reply
    Greg_Deckler
    Super User Greg_Deckler
    Super User
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎12-02-2019 05:32 AM

    Yep, shouldn't be any problem.


    @ 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...
    Message 5 of 10
    9,387 Views
    1
    Reply
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-19-2018 08:26 AM

    Points for creativity!

    Message 2 of 10
    11,430 Views
    1
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎08-05-2019 05:46 AM

    This is beautiful

    Message 3 of 10
    10,141 Views
    0
    Reply

    Power Platform

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

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • 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
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices