topic Re: Multi-Column Aggregations (MC Aggregations) in Quick Measures Gallery

Multi-Column Aggregations (MC Aggregations)

Greg_Deckler — Mon, 18 Jun 2018 20:44:37 GMT

"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

Re: Multi-Column Aggregations (MC Aggregations)

Anonymous — Tue, 19 Jun 2018 15:26:45 GMT

Points for creativity!

Re: Multi-Column Aggregations (MC Aggregations)

Anonymous — Mon, 05 Aug 2019 12:46:34 GMT

This is beautiful

Re: Multi-Column Aggregations (MC Aggregations)

Anonymous — Mon, 02 Dec 2019 05:23:09 GMT

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.

Re: Multi-Column Aggregations (MC Aggregations)

Greg_Deckler — Mon, 02 Dec 2019 13:32:59 GMT

Yep, shouldn't be any problem.

Re: Multi-Column Aggregations (MC Aggregations)

dmalque — Wed, 22 Dec 2021 16:58:16 GMT

como podria omitir los ceros al calcular el promedio

Re: Multi-Column Aggregations (MC Aggregations)

EtnaEstrella — Tue, 25 Jan 2022 05:45:52 GMT

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?

Re: Multi-Column Aggregations (MC Aggregations)

EtnaEstrella — Tue, 25 Jan 2022 05:58:10 GMT

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

Re: Multi-Column Aggregations (MC Aggregations)

gsksarepta — Mon, 21 Nov 2022 14:53:02 GMT

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.

Re: Multi-Column Aggregations (MC Aggregations)

Greg_Deckler — Mon, 21 Nov 2022 19:40:33 GMT

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