Add a line for average of percentages

[deedeedudu]

Hi,

I have the following data set:

I have created a measure to calculate percentage as

Percentage = SUM(Sheet1[Numerator])/SUM(Sheet1[Denominator])

And charted it over time axis and then added average line from Analytics option

The problem is- It is actually calculating average of percentages for two months = (42.86%+85%)/2=63.93%

which is not the correct way of taking average. It should be actually 74.07%

= 20/27

[v-xiaotang]

Hi @deedeedudu 

Thanks for reaching out to us. 

For average, it does take the average(63.93%) of all endpoints instead of 74.07%. If you want to get 74.07%, you need to create a measure, and change the visual to those that support line.

line = IF(MIN(Sheet1[Date])<>BLANK(),SUMX(ALL(Sheet1),Sheet1[Numerator])/SUMX(ALL(Sheet1),Sheet1[Denominator])) 

Best Regards,

Community Support Team _Tang

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

[deedeedudu]

Thanks for the reply. Is there any way I can take average of last n months instead of selecting All? 

[v-xiaotang · Accepted solution]

Hi @deedeedudu 

If you want to take average of last n months, just filter the table when you do sumx() operation, such like, 

line =
VAR _start =
    EOMONTH ( today, - n ) //  it is suggested to use eomonth instead of edate in this scenario
RETURN
    IF (
        MIN ( Sheet1[Date] ) <> BLANK (),
        SUMX ( FILTER ( ALL ( Sheet1 ), Sheet1[Date] > _start ), Sheet1[Numerator] )
            / SUMX ( FILTER ( ALL ( Sheet1 ), Sheet1[Date] > _start ), Sheet1[Denominator] )
    )

Best Regards,

Community Support Team _Tang

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