skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Retail
    • For analysts
    • For IT
    • For developers
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
      • Overview
      • Guided learning
      • Documentation
      • Webinars
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Galleries
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • MBAS Gallery
    • MBAS Gallery 2020
    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
    • The New Hotness (Custom Matrix Hierarchy)

    The New Hotness (Custom Matrix Hierarchy)

    03-08-2020 09:42 AM - last edited 03-22-2020 14:40 PM

    Super User IV Greg_Deckler
    Super User IV
    1800 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Greg_Deckler
    Super User IV Greg_Deckler
    Super User IV
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    The New Hotness (Custom Matrix Hierarchy)

    ‎03-08-2020 09:42 AM

    OK, this one is a bit complex and is in many ways a different spin on the Disconnected Table Trick.

    So, this recipe is designed to fix the issue where you have a matrix with a column or two in it and you just want to add a value onto the the end of the matrix but then the matrix decides to spew out the value for every column in the matrix and you end up trying to shrink the offending columns down so that you don't really see them, but it is tedious and ugly and generally ends up looking pretty terrible and so you are like "Ah man, this sucks, my report was looking great but now I have this jankey matrix visual that looks terrible" and you think to yourself "If only there was a way to tack columns on to the end of the matrix sort of like Totals" so then you go look in the Subtotals and Grand Totals areas of the formatting pane but you don't find anything and you realize that is probably because these extra columns wouldn't really be totals per se but since they are columns you think "Yeah, maybe in the Column headers area" but you don't find anything there either but you do notice the Grid area right above it so you check that out because, you know, what the heck, but there's nothing really helpful there so in desparation you check the Values area and still no joy and you think "Hey maybe if I click on the little drop down arrow on the column in the Values area maybe there is an option like show only once" so you check that and spend a couple hours playing around with Conditional formatting, which you had never really noticed before but then you get back to the problem at hand and you are left frustrated, depressed and all downtroddin and stuff thinking, "Yo dawg, if I could just have some option somewhere where I could just tack a few extra columns on to the end of the matrix just like I can keep tacking words on to the end of a sentence..." Bummer.

    So if you have ever found yourself in that situation. I mean, not that EXACT situation because that is perhaps oddly specific. But, you know, generally that situation. You can do this, create your own custom hierarchy like this:

     

    Custom Hierarchy = 
        { 
            ("2019", "January", 1),
            ("2019", "February", 2),
            ("2019", "March", 3),
            ("2019", "April", 4),
            ("2019", "May", 5),
            ("2019", "June", 6),
            ("2019", "July", 7),
            ("2019", "August", 8),
            ("2019", "September", 9),
            ("2019", "October", 10),
            ("2019", "November", 11),
            ("2019", "December", 12),
            ("2020", "January", 1),
            ("2020", "February", 2),
            ("2020", "March", 3),
            ("2020", "April", 4),
            ("2020", "May", 5),
            ("2020", "June", 6),
            ("2020", "July", 7),
            ("2020", "August", 8),
            ("2020", "September", 9),
            ("2020", "October", 10),
            ("2020", "November", 11),
            ("2020", "December", 12),
            ("Other", "LY", 13),
            ("Other", "PY", 14)
        }

     

     

     

    And then create a corresponding measure like this:

     

     

     

    Measure = 
        VAR __Value1 = MAX('Custom Hierarchy'[Value1])
        VAR __Value2 = MAX('Custom Hierarchy'[Value2])
    RETURN
        SWITCH(
            TRUE(),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "January",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 1
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "February",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 2
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "March",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 3
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "April",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 4
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
           ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "May",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 5
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "June",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 6
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "July",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 7
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "August",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 8
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "September",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 9
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
           ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "October",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 10
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "November",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 11
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "December",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                VAR __Month = 12
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year && MONTH('Table'[Invoice Date]) = __Month),[Price]), 
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "LY",1000,
            ISINSCOPE('Custom Hierarchy'[Value2]) && __Value2 = "PY",100,
            ISINSCOPE('Custom Hierarchy'[Value2]), BLANK(),
            ISINSCOPE('Custom Hierarchy'[Value1]) && __Value1 <> "Other",
                VAR __Year = VALUE(MAX('Custom Hierarchy'[Value1]))
                RETURN
                    SUMX(FILTER('Table',YEAR('Table'[Invoice Date]) = __Year),[Price]),
            ISINSCOPE('Custom Hierarchy'[Value1]) && __Value1 <> "Other",
                BLANK(),
            BLANK()
        )

     

     

     

     

     

     

    eyJrIjoiMDllYzdkMjEtNjdlOS00OWQ1LWE0N2YtMmEwMGRiN2JjOTg4IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9


    ---------------------------------------

    @ me in replies or I'll lose your thread!!!

    I have a NEW book! 
    DAX Cookbook from Packt
    Over 120 DAX Recipes!




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

    Proud to be a Super User!




    Preview file
    51 KB
    Impossible.pbix
    Labels:
    • Labels:
    • Other
    Message 1 of 3
    1,800 Views
    4
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    CNENFRNL
    CNENFRNL Community Champion
    Community Champion
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-30-2020 01:14 PM

    Another piece from your colleciton of ingenious works!

    Please produce Customer Hierarchy table in PQ, if you don't mind.😉

    let
        Source = Table.FromRecords(
            List.Generate(
                () => {Date.StartOfYear(Date.AddYears(DateTime.LocalNow(), -1))},
                each Date.Year(_{0}) <= Date.Year(Date.AddYears(DateTime.LocalNow(), 0)),
                each {Date.AddMonths(_{0}, 1)},
                each [Year = Date.Year(_{0}), Month = Date.Month(_{0}), MonthName = Date.MonthName(_{0})]
            )
        )
        &
        #table({"Year", "Month", "MonthName"}, {{"Other", "LY", 13}, {"Other", "PY", 14}})
    in
        Source

     

    Message 2 of 3
    986 Views
    2
    Reply
    Greg_Deckler
    Super User IV Greg_Deckler
    Super User IV
    In response to CNENFRNL
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    ‎08-30-2020 01:45 PM

    @CNENFRNL - Oh yeah, I like that! Very nice! I wish my Power Query fu was stronger sometimes! 🙂


    ---------------------------------------

    @ me in replies or I'll lose your thread!!!

    I have a NEW book! 
    DAX Cookbook from Packt
    Over 120 DAX Recipes!




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

    Proud to be a Super User!




    Message 3 of 3
    981 Views
    0
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • 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

    • © 2021 Microsoft
    • Follow Power BI
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks