topic Re: The New Hotness (Custom Matrix Hierarchy) in Quick Measures Gallery

The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Sun, 22 Mar 2020 21:40:44 GMT

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

Re: The New Hotness (Custom Matrix Hierarchy)

CNENFRNL — Sun, 30 Aug 2020 20:14:35 GMT

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

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Sun, 30 Aug 2020 20:45:43 GMT

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

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Fri, 05 Feb 2021 03:12:42 GMT

I love this solution. Do you know if it is possible to conditionally format specific columns e.g. Ly, PY?

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Thu, 16 Sep 2021 09:24:10 GMT

Thanks Greg for this solution, it is helping me immensely. I have customized it to fit my needs but struggle with column subtotals. It seems that the matrix cannot compute them for the additional values - LY and PY in your example, and gives blank values. For row subtotals instead it works perfectly. Any idea on what may cause this behavior?

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Thu, 16 Sep 2021 09:36:44 GMT

Anonymous If I understand the question correctly, once you go this route I believe you would need to add the Total columns to your hierarchy essentially and compute them yourself. If you had sample data and expected output I might be able to be more specific.

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Thu, 16 Sep 2021 11:24:34 GMT

Greg_Deckler unfortunately i cannot provide sample data. Using your example, just imagine that LY and PY are measures that can be filtered by the rows. I would then like to have a column total which sums the monthly data with LY and PY.

Just to make sure I understand your suggestion, you are saying to create a further column in the measure which sums up the data, insted of relying on the built in column subtotal of matrix visual, is that correct? Thanks for your help

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Thu, 16 Sep 2021 11:39:12 GMT

Anonymous Correct, that's what I'm thinking.

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Wed, 06 Jul 2022 03:54:48 GMT

Hi ceyres,

Did you find the way how to add conditional format using this way?

Re: The New Hotness (Custom Matrix Hierarchy)

Aurelio — Thu, 01 Dec 2022 14:32:23 GMT

Greg_Deckler Interesting look at a very annoying problem. The solution works fine but is there a way to insert additional colums after every year?

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Thu, 01 Dec 2022 16:25:29 GMT

Aurelio I don't see why not.

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Tue, 05 Sep 2023 14:33:15 GMT

Hey Greg_Deckler

I have a question, is this something also related to the measures we have in values bucket but we wanted to add individual measure not getting affected by the column bucket layer ?

Re: The New Hotness (Custom Matrix Hierarchy)

autoNation — Fri, 15 Mar 2024 16:19:30 GMT

Can you post the PBIX ?

I'm a little confused on how to relate this to a date table and which value 1 and value 2 are refering too.

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Fri, 15 Mar 2024 18:10:15 GMT

autoNation The PBIX is attached. It is named Impossible.pbix and is just above the Reply button but over on the left.

Re: The New Hotness (Custom Matrix Hierarchy)

Anonymous — Wed, 02 Oct 2024 09:38:44 GMT

Thank you this was very helpful.

Unfortunately I am having trouble getting my columns in correct order.
I have added the value3 column, same as you, to give the correct order but I am not sure how to apply it.

Any help would be most appreciated

Re: The New Hotness (Custom Matrix Hierarchy)

Greg_Deckler — Wed, 02 Oct 2024 14:45:57 GMT

Anonymous You need to use the Sort By column functionality. So set the Sort By column for your second column to be your third column