Multi-Column Aggregations (MC Aggregations) 04-07-2018 15:53 PM - last edited 06-18-2018 13:44 PM

[Greg_Deckler]

"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

[gsksarepta]

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. 

[Greg_Deckler]

@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

[EtnaEstrella]

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]

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

[dmalque]

como podria omitir los ceros al calcular el promedio

[Anonymous]

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. 

[Greg_Deckler]

Yep, shouldn't be any problem.

[Anonymous]

Points for creativity!

[Anonymous]

This is beautiful