Forum Discussion
show Average minimum max in 1 table
Assuming all of your data is loaded unpivoted as you have shown, you could add a Matrix visual and place the Measure into the 'Rows' box of the visuals setting.
In the values box of the visual setting, place each of the number columns you want to get an average of. Next, click on the drop arrow next to the name of the field and you can change the aggregation type. You can select "Average". This will average the values by each of the measure types in your rows section.
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?
