show Average minimum max in 1 table

[apmehta]

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 ?

[Anonymous]

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.

[apmehta]

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 ?

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

Bit of a better illustration that it works:

[apmehta]

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 ?

[apmehta]

The first pic of my original post is the source data (pre aggregated in a csv file) over each "distance" value. So for every distance value i have 4 Measure Type. As per below pic:

What I want to show from the above is 

you see i have removed the distance field and then it shows me jst 1 row against each measure type. So want to show this condensed AS IS in powerbi, somehow.

Hope it makes sense now ?

[Anonymous]

Hi @apmehta  Sorry it took so long to response, some unexpected events came up that took me away for a few days.

Based on what your last post has described, these are the steps you'd need to do:

Firstly we need to unpivot your data.  I entered your sample data using "Enter Data" and then performed the following Power Query Code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZI9a8MwEIb/itAcXvRpSaNp1y4NnUwGhYhimqRgktD+++okU9StdpvhOPtsntd6fMPA+1ua4mviG94f9/H8yR6mlN7Y9rpn/XRJ0xiP7Pk9HthLMPklkUvmMpAd3aILNNEwOncHQVNloQ3fbQb+FD/G0/W0lN7BEk4hEF1bGjtQswFCV/R4XoNW83UpCFVibEVuLwewx3TDUqiE96UVKdJA+HIKV04hSQ7xl7qGkGH+ZA1XR+VJgKNh1kwBMlDQGt1NQAdfkUXJbNvDUdMKzq+S3gTg29hP9Wql+gad9ZctrI48TDXmy6/J9u1f7N9x25uEe2x8g//frW/Av9n83Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Measure Type" = _t, #"Road Name" = _t, distance = _t, carriageway = _t, #"run-off road" = _t, #"Head on" = _t, Intersection = _t, Other = _t, Total = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Measure Type", type text}, {"Road Name", type text}, {"distance", type number}, {"carriageway", Int64.Type}, {"run-off road", type number}, {"Head on", type number}, {"Intersection", type number}, {"Other", type number}, {"Total", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Measure Type", "Road Name"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Hopefully that gives you an idea of how to unpivot.

Next I used a matrix visual and  put the Measure Type and Road Type as Rows, Attibute as Column and Value as Value.  I set the value to average, but you can choose what is approprate