Forum Discussion

ashleylinkewich's avatar
ashleylinkewich
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Add average bar to ribbon/bar chart

Hi everyone,

 

Not sure if this is possible, but I am wondering if it's possible to add an average bar on the right, as if it was another date. 

 

I.e on my x axis here having all of my dates on the left and then "Average" on the right, with the average of the previous values for their respective ribbons. I have attached a photo below... 

 

My average ribbon on the right would have 5.3 for the green ribbon, 2.1 for the light blue, etc. 

 

 

 

Is this even a possibility? Could I create a calculation using the earlier function? Not sure how I would sort that by my legend though.

 

Hopefully this makes sense - thanks in advance! 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi ashleylinkewich , 

    You could use M code to add avg rows in data sample.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7DcAgDAV3cY0E/iCGQRQh7D9DXLhIXly85uQ7yXOSNGl1VKZCl6/TKh+4fYzw9gnCg7pEkxFu1CWa+oaa6ZrpGnpHePDSoqkIf29a9qZFc9BaDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, #"type" = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"type", type text}, {"amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"type"}, {{"amount", each List.Average([amount]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "date", each "avg line"),
    #"Appended Query" = Table.Combine({#"Added Custom", #"Changed Type"})
in
    #"Appended Query"

     You could refer to my sample for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi ashleylinkewich , 

    You could use M code to add avg rows in data sample.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+7DcAgDAV3cY0E/iCGQRQh7D9DXLhIXly85uQ7yXOSNGl1VKZCl6/TKh+4fYzw9gnCg7pEkxFu1CWa+oaa6ZrpGnpHePDSoqkIf29a9qZFc9BaDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, #"type" = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"type", type text}, {"amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"type"}, {{"amount", each List.Average([amount]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "date", each "avg line"),
    #"Appended Query" = Table.Combine({#"Added Custom", #"Changed Type"})
in
    #"Appended Query"

     You could refer to my sample for details.

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.