Forum Discussion
show Average minimum max in 1 table
You can select "Average". This will average the values by each of the measure types in your rows section >> Thats where i am getting stuck as I dont want the whole column to be averaged but just that ROW - Average, then do a Max - for that whole ROW.. so need aggregation done on Row Level and not Col level.
Makes sense ?
I think i possibly understand what you are chasing. Doing what I've described will cause the matrix to look at only the figures that are on that row (i.e. all of the figures of that measure type). The figure displayed in that box will be an Average of the figures that match that measure type for that column. If we select to show a row total, you'll get an average of all the figures.
So if we want to get an average of each of the value types, within 1 measure type, we'll need to unpivot your data further. This can be done as part of the import query. So presently you have 1 row which has 5 numbers on it. By unpivoting we'll bring down the 4 you want to average and get a maximum of.
From here we can write a DAX statement that will do the averages on a value type basis, then we can get a max of that value type.
I'll write up an example version of what this might look like in my next reply.
Ok here is some examples to get you on your way:
The Power Query code should be easy to generate from the "Unpivot Columns" under "Transform" tab of the ribbon. You should get some code that looks something like:
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"YourPreviousLine", {"MeasureType"}, "Attribute", "Value")From here we need a simple average measure
Average Value = AVERAGE(ExampleTable[Value])
Next is a measure to calculate the maximum
Max of Average = MAXX( VALUES(ExampleTable[Attribute]), [Average Value] )Lastly we need to put it into a table or matrix with the Measure Type on the rows (i only used a few rows of your data)
Is that more what you were hoping to see?
Bit of a better illustration that it works:
Hi Ross
Unfortunately this is doing Max over the averages and then general AVG on the whole dataset.. not what i am looking for.
In my initial dataset as shown in either pic1 or pic2 its set to "Dont Summarize".
So just need to display the values as is against the field "Measuretype" without plotting it against each "distance" field value but "intersection" etc .
I tried something like this, creating a measure on the fly to calculate Avg / Max
Run-off road (avg) = CALCULATE(AVERAGE('ARisk Score Results'[Run-off road]),FILTER('ARisk Score Results', 'ARisk Score Results'[MeasureType]="Average"))and what i was getting in form of table is as per the pic below
As you will see they are not falling under Col Run-off road and so on, so tinkering with it to get in same format as pic2 of original post.
Also tried using If / Else statement but
Column = if('ARisk Score Results'[MeasureType] = "Average", CALCULATE(AVERAGE('ARisk Score Results'[Run-off road])),CALCULATE(Max('ARisk Score Results'[Run-off road])))But doesnt work either as it does AVG on the whole col of Run-off road (including avg for max / min / std dev).
Thoughts anyone ?