Forum Discussion
create a bar chart with an average line running through
Hi All,
I am trying to create a bar chart with an average line running through. The matrix button works as below:-
[CODE]
| Function | % Complete |
| Function 1 | 54.50% |
| Function 2 | 40.00% |
| Function 3 | 34.60% |
| Function 4 | 33.30% |
| Function 5 | 23.20% |
| Function 6 | 21.40% |
| Function 7 | 20.00% |
| Function 8 | 18.50% |
| Function 9 | 17.80% |
| Function 10 | 17.80% |
| Function 11 | 17.50% |
| Function 12 | 16.60% |
| Function 13 | 14.90% |
| Function 14 | 13.50% |
| Function 15 | 13.20% |
| Function 16 | 12.80% |
| Function 17 | 11.80% |
| Function 18 | 10.20% |
| Function 19 | 10.10% |
| Function 20 | 9.40% |
| Function 21 | 8.90% |
| Function 22 | 8.80% |
| Function 23 | 8.00% |
| Function 24 | 7.90% |
| Function 25 | 6.70% |
| Function 26 | 6.70% |
| Function 27 | 5.80% |
| Function 28 | 5.10% |
| Function 29 | 3.10% |
| Function 30 | 2.90% |
| Function 31 | 2.60% |
| Function 32 | 2.60% |
| Function 33 | 2.60% |
| Function 34 | 1.90% |
| Function 35 | 0.00% |
| Function 36 | 0.00% |
| Function 37 | 0.00% |
| Function 38 | 0.00% |
[/CODE]
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:-
[CODE]% Complete = IF(ISBLANK([Survey Complete]/([Survey Complete]+[Survey Not Complete])),0,[Survey Complete]/([Survey Complete]+[Survey Not Complete]))[/CODE]
I also tried to create an average measure (which calculates correctly);-
[CODE]Average = Divide(Calculate([Survey Complete],ALLSELECTED(HeadcountData)),Calculate([Eligible Employees],ALLSELECTED(HeadcountData)))[/CODE]
However, I am unable to create the graph with the correct average over the functions. Any help would be greatly appreciated.
Kind regards,
Forrestgump
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
12 Replies
- Greg_Deckler
Community Champion
You 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
