How to calculate an average line in the Line Chart ?

[siva3012]

Hi, I want to calculate an average line for 3 days across the time interval period. I want to get an average Black line as shown in the below dig.  

Can anyone guide me with the solution.

[v-yulgu-msft · Accepted solution]

Hi @siva3012,

Based on my test, if we have already add a legend for line chart, it is not allowed to add multiple columns or measures into values section. In other words, we can generate a measure to return average value, but we are unable to add this measure to line chart. 

As a workaround, we can create multiple measures to get result for each legend item separately.

Base on below sample data, we could create measures like:

Sum for 1 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=1))
Sum for 2 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=2))
Sum for 3 = CALCULATE(SUM('Table'[DurationDays]),FILTER('Table','Table'[ID]=3))

Average =
CALCULATE (
    SUM ( 'Table'[DurationDays] ),
    ALLEXCEPT ( 'Table', 'Table'[Type] )
)
    / CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        ALLEXCEPT ( 'Table', 'Table'[Type] )
    )

Best regards,
Yuliana Gu

[Blake753]

How would you then add a filter on top of this to see just the average line and say "Sum for 1"?  Essentially filtering the measures?

[siva3012]

@v-yulgu-msft : Thanks for the help. I also think this is the only way to approach. 

I have one query - How can i calculate the percentage between the Average line & other lines 1,2&3.

In simple words i want to know whether these values/lines (1,2&3) will be 5% +/- from the average line ?

Do you have any idea about it ?

[v-yulgu-msft]

Hi @siva3012,

Have you achieved your requirement? If yes, would you please mark the helpful reply as an answer or share your solution so that it can benefit more users?

Regards,
Yuliana Gu

[v-yulgu-msft]

Hi @siva3012,

Since the sum values and average values are all returned by measures, we can directly compute the percentage via:

Percentage1 = ([Sum for 1]-[Average])/[Average]

Percentage2 = ([Sum for 2]-[Average])/[Average]

Percentage3 = ([Sum for 3]-[Average])/[Average]

Regards,
Yuliana Gu

[Anonymous]

Hi @siva3012

Have you tried using adding an average line from Analytics pane?

[siva3012]

@Anonymous : I tried the average line from Analytics plane and it draws an straingt line , i have shown it in the dig. 

[Anonymous]

Yeah, I missed that.. Let me try another way