Forum Discussion
create a bar chart with an average line running through
Hi Forrestgump,
For column chart, we can only add the measure as a Min line in the Analytics pane like this.
Measure = CALCULATE(DISTINCTCOUNT(SurveyData[id]),ALL(SurveyData))/CALCULATE(COUNTROWS(HeadcountData),ALL(SurveyData))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/g56davhns5i6kt1/create%20a%20bar%20chart.pbix?dl=0
Regards,
Frank
Community ChampionYou mean like this?
Hi Greg,
Thanks for your reply. If the dotted line running through the chart is 10.8 then yes. Is the dotted line 10.8%?
Kind regards,
Forrest
- Greg_Deckler
The average in Excel is 12.8%. The one displayed in Power BI is .13 (same if you increase decimal count). I'm not understanding how you are getting your average as your method seems to disagree with Excel. Apparently your concept of average is different than the standard way of conceptualizing average. What you are asking for is the overall % complete, not an average of % complete. You would do that by creating a measure that essentially does this:
Measure = COUNTX(FILTER(ALL('Table'),[Column]="Complete"),[Column]) / COUNTX(ALL('Table'),[Column])
Difficult to say exactly without source data, which is what I thought you provided but apparently not. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
hi Greg,
Thanks for your reply. So i am looking for the 10.8% which comes form the overall calculation:-
SPU / Function # Complete Eligible Employees Percentage Function 1 18 33 54.5% Function 2 14 35 40.0% Function 3 18 52 34.6% Function 4 1 3 33.3% Function 5 60 259 23.2% Function 6 72 336 21.4% Function 7 1 5 20.0% Function 8 51 275 18.5% Function 9 8 45 17.8% Function 10 95 535 17.8% Function 11 7 40 17.5% Function 12 41 247 16.6% Function 13 10 67 14.9% Function 14 66 489 13.5% Function 15 18 136 13.2% Function 16 18 141 12.8% Function 17 11 93 11.8% Function 18 11 108 10.2% Function 19 30 298 10.1% Function 20 14 149 9.4% Function 21 28 313 8.9% Function 22 3 34 8.8% Function 23 30 374 8.0% Function 24 34 428 7.9% Function 25 6 89 6.7% Function 26 58 867 6.7% Function 27 12 207 5.8% Function 28 29 573 5.1% Function 29 6 191 3.1% Function 30 4 137 2.9% Function 31 1 38 2.6% Function 32 5 190 2.6% Function 33 2 77 2.6% Function 34 7 368 1.9% Function 35 0 5 0.0% Function 36 0 17 0.0% Function 37 0 11 0.0% Function 38 0 7 0.0% Total 789 7272 10.8% 
v-frfei-msftCommunity Support
Hi Forrestgump,
For column chart, we can only add the measure as a Min line in the Analytics pane like this.
Measure = CALCULATE(DISTINCTCOUNT(SurveyData[id]),ALL(SurveyData))/CALCULATE(COUNTROWS(HeadcountData),ALL(SurveyData))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/g56davhns5i6kt1/create%20a%20bar%20chart.pbix?dl=0
Regards,
Frank