Forum Discussion
show Average minimum max in 1 table
Hi,
I have a precalculated all AVG / Min / Max / Std Dev in an excel file (currently have to use this a source). The measures created are calculated for each value under Field "distance". So in doing so I get approx 200 rows (narrowed on filter for Field_R).
So what i get is the long list of table values as shown in AllMeasure_Pic1 (Attached).
However I want to show a condesed version to show only 1 row per MeasureType as shown below:
What i can see is that,If i remove field Field "distance" from my underlying source (yes, remove the col from excel sheet all together) then i get a good summarised table (Pic 2) but as soon as i incorporate field "distance" in the source and then try to summarise the table then i get values against each value as shown in pic 1, though Logically absolutely correct.
However I need to use the field "distance" - to show in the graph... and hence cannot do away with it. So whats the other way to get condensed version of summarised table.
My Graph is based on X-Axis - Total (last col from Pic 1 or pic 2) and y-axis is "distance" and values is as shown in pic1 and pic2 as well (Runoff / Intersection / Head on etc..)
Hope it makes sense ?
8 Replies
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.
