Forum Discussion

apmehta's avatar
apmehta
Frequent Visitor
8 years ago

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 d...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Bit of a better illustration that it works:

apmehta's avatar
apmehta
Frequent Visitor
8 years ago

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's avatar
    apmehta
    Frequent Visitor
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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