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

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'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