topic Line Chart: Average of all Selected Lines during timestamp in Developer

Line Chart: Average of all Selected Lines during timestamp

josh122398 — Thu, 04 May 2023 16:23:22 GMT

Hello,

I tried to keep an average of all selected lines (during each timestamp) on my line chart. However, I don't think PowerBI allows me to keep this curve due to the way legends interact with the data. See below what I mean:

With the legend:

Without the legend:

In effect, I want the bottom curve (without the legend) overlayed with the top image's curves. Note, the curve without the legend will shift when I select/deselect the various legend values. I want this line to overlay with all unique legend values, and average the selected legend values when specified.

Hope this helps describe my issue in enough depth.

Re: Line Chart: Average of all Selected Lines during timestamp

lbendlin — Mon, 08 May 2023 00:48:11 GMT

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.powerbi.com

Re: Line Chart: Average of all Selected Lines during timestamp

v-jianboli-msft — Fri, 26 May 2023 08:56:53 GMT

Hi josh122398 ,

Here is my sample:

Please try:

First create a new table like this:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xDcAwCETRXahdGDBJ2iRjWOy/hk1ko0txzZO+oHeSKkKF7jmu5CXlCTlQ3jmxLboqZZSo5CdRcUpblTWUqFRQorK8bvvDE+WrGkpUepH7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Legend = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Legend", type text}, {"Value", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Legend"}), #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"), #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each {_[Legend],"Average"}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom"

Then build relationship between the two tables:

Create a slicer:

Apply the measure to the line chart:

Measure = VAR _a = ALLSELECTED ( 'Table'[Legend] ) RETURN IF ( SELECTEDVALUE ( Legend[Custom] ) = "Average", CALCULATE ( AVERAGE ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Legend] IN _a && [Year] = SELECTEDVALUE ( 'Table'[Year] ) ) ), SUM ( 'Table'[Value] ) ) Measure = VAR _a = ALLSELECTED ( 'Table'[Legend] ) RETURN IF ( SELECTEDVALUE ( Legend[Custom] ) = "Average", CALCULATE ( AVERAGE ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), [Legend] IN _a && [Year] = SELECTEDVALUE ( 'Table'[Year] ) ) ), SUM ( 'Table'[Value] ) )

Final output:

Best Regards,

Jianbo Li

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