Multi-Column Aggregations (MC Aggregations)
"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
9 Replies
Yep, shouldn't be any problem.
como podria omitir los ceros al calcular el promedio
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?
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
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.
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
