Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Community 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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
12 REPLIES 12
Super User

You mean like this?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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%
Community 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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

Thanks, Frank, Greg for all your help!

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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)

Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

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

Community Support

Hi @Forrestgump

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

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

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?

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors