create a bar chart with an 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:-

[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

[Greg_Deckler]

You mean like this?

[Forrestgump]

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

[Forrestgump]

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-msft · Accepted solution]

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

[Forrestgump]

Thanks, Frank, Greg for all your help! 

[Greg_Deckler]

Measure = SUM('Table'[# Complete]) / SUM('Table'[Eligible Employees])

[Forrestgump]

The issue with this is that [#Complete] and [Eligible Employees] are measures not columns so this won't work i believe.

# Complete = DistinctCount(SurveyData[ID])

Eligible Employees = CountRows(HeadcountData)

[v-frfei-msft]

Hi @Forrestgump,

Here I made one sample for your reference.

1. Create the measures as you offered. 

# Complete = DistinctCount(SurveyData[ID])

Eligible Employees = CountRows(HeadcountData)

2. Then create a measure using the formula and create a Line and stacked column chart to work around.

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

[Forrestgump]

Hi Frank,

That worked great.....However the bar chart I wanted to create runs from right to left. Is there a way of achieving this?

Regards,

Forrest

[v-frfei-msft]

Hi @Forrestgump，

You can sort the visual what you need by this way.

Regards,

Frank

[Forrestgump]

Hi Frank,

Sorry what i mean is a bar chart not a column chart but there doesn't seem to be an option for a bar chart for line and clustered bar chart?