Create Bar Chart With Average Line running through

[forrestgump]

Hi All,

I am trying to create a bar chart with an average line running through. The matrix button works as below:-

[TABLE="width: 364"]
<tbody>[TR]
[TD]Function[/TD]
[TD]% Complete[/TD]
[/TR]
[TR]
[TD]Function 1[/TD]
[TD="align: right"]54.50%[/TD]
[/TR]
[TR]
[TD]Function 2[/TD]
[TD="align: right"]40.00%[/TD]
[/TR]
[TR]
[TD]Function 3[/TD]
[TD="align: right"]34.60%[/TD]
[/TR]
[TR]
[TD]Function 4[/TD]
[TD="align: right"]33.30%[/TD]
[/TR]
[TR]
[TD]Function 5[/TD]
[TD="align: right"]23.20%[/TD]
[/TR]
[TR]
[TD]Function 6[/TD]
[TD="align: right"]21.40%[/TD]
[/TR]
[TR]
[TD]Function 7[/TD]
[TD="align: right"]20.00%[/TD]
[/TR]
[TR]
[TD]Function 8
[/TD]
[TD="align: right"]18.50%[/TD]
[/TR]
[TR]
[TD]Function 9[/TD]
[TD="align: right"]17.80%[/TD]
[/TR]
[TR]
[TD]Function 10
[/TD]
[TD="align: right"]17.80%[/TD]
[/TR]
[TR]
[TD]Function 11[/TD]
[TD="align: right"]17.50%[/TD]
[/TR]
[TR]
[TD]Function 12[/TD]
[TD="align: right"]16.60%[/TD]
[/TR]
[TR]
[TD]Function 13[/TD]
[TD="align: right"]14.90%[/TD]
[/TR]
[TR]
[TD]Function 14[/TD]
[TD="align: right"]13.50%[/TD]
[/TR]
[TR]
[TD]Function 15[/TD]
[TD="align: right"]13.20%[/TD]
[/TR]
[TR]
[TD]Function 16[/TD]
[TD="align: right"]12.80%[/TD]
[/TR]
[TR]
[TD]Function 17[/TD]
[TD="align: right"]11.80%[/TD]
[/TR]
[TR]
[TD]Function 18[/TD]
[TD="align: right"]10.20%[/TD]
[/TR]
[TR]
[TD]Function 19[/TD]
[TD="align: right"]10.10%[/TD]
[/TR]
[TR]
[TD]Function 20[/TD]
[TD="align: right"]9.40%[/TD]
[/TR]
[TR]
[TD]Function 21[/TD]
[TD="align: right"]8.90%[/TD]
[/TR]
[TR]
[TD]Function 22[/TD]
[TD="align: right"]8.80%[/TD]
[/TR]
[TR]
[TD]Function 23[/TD]
[TD="align: right"]8.00%[/TD]
[/TR]
[TR]
[TD]Function 24[/TD]
[TD="align: right"]7.90%[/TD]
[/TR]
[TR]
[TD]Function 25
[/TD]
[TD="align: right"]6.70%[/TD]
[/TR]
[TR]
[TD]Function 26[/TD]
[TD="align: right"]6.70%[/TD]
[/TR]
[TR]
[TD]Function 27[/TD]
[TD="align: right"]5.80%[/TD]
[/TR]
[TR]
[TD]Function 28[/TD]
[TD="align: right"]5.10%[/TD]
[/TR]
[TR]
[TD]Function 29[/TD]
[TD="align: right"]3.10%[/TD]
[/TR]
[TR]
[TD]Function 30[/TD]
[TD="align: right"]2.90%[/TD]
[/TR]
[TR]
[TD]Function 31[/TD]
[TD="align: right"]2.60%[/TD]
[/TR]
[TR]
[TD]Function 32[/TD]
[TD="align: right"]2.60%[/TD]
[/TR]
[TR]
[TD]Function 33[/TD]
[TD="align: right"]2.60%[/TD]
[/TR]
[TR]
[TD]Function 34[/TD]
[TD="align: right"]1.90%[/TD]
[/TR]
[TR]
[TD]Function 35[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Function 36[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Function 37[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]Function 38
[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]

The average over the total should be 10.8% because the total number of completes for all functions is 789 and the total number eligible is 7272 which is 10.8%. However, when i turn on the average line in power bi it comes out at 12.8%. Which is basically all the percentages added together 484.7% divide by the number of functions i.e. 38 = 12.8%.

The formula I am using for the percentage calc is:-

% Complete = IF(ISBLANK([Survey Complete]/([Survey Complete]+[Survey Not Complete])),0,[Survey Complete]/([Survey Complete]+[Survey Not Complete]))

I also tried to create an average measure (which calculates correctly);-

Average = Divide(Calculate([Survey Complete],ALLSELECTED(HeadcountData)),Calculate([Eligible Employees],ALLSELECTED(HeadcountData)))

However, I am unable to create the graph with the correct average over the functions. Any help would be greatly appreciated.

Kind regards,

Forrestgump

 

[citizenbh]

Make new column Avg:

Avg=SUM(Table1[% Complete])/COUNT([Function])

Insert Combo Chart, with bar chart ([%Complete]) and line chart ([Avg])

 

[Jon Peltier]

Actually, I just rewrote a tutorial on this very subject:
Add a Horizontal Line to an Excel Chart