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

Reply
Forrestgump
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 154.50%
Function 240.00%
Function 334.60%
Function 433.30%
Function 523.20%
Function 621.40%
Function 720.00%
Function 818.50%
Function 917.80%
Function 1017.80%
Function 1117.50%
Function 1216.60%
Function 1314.90%
Function 1413.50%
Function 1513.20%
Function 1612.80%
Function 1711.80%
Function 1810.20%
Function 1910.10%
Function 209.40%
Function 218.90%
Function 228.80%
Function 238.00%
Function 247.90%
Function 256.70%
Function 266.70%
Function 275.80%
Function 285.10%
Function 293.10%
Function 302.90%
Function 312.60%
Function 322.60%
Function 332.60%
Function 341.90%
Function 350.00%
Function 360.00%
Function 370.00%
Function 380.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

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

asdasd.PNG

 

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.

View solution in original post

12 REPLIES 12
Greg_Deckler
Super User
Super User

You mean like this?

 

image.png


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

hi Greg,

 

Thanks for your reply. So i am looking for the 10.8% which comes form the overall calculation:-

 

SPU / Function# CompleteEligible EmployeesPercentage
Function 1183354.5%
Function 2143540.0%
Function 3185234.6%
Function 41333.3%
Function 56025923.2%
Function 67233621.4%
Function 71520.0%
Function 85127518.5%
Function 984517.8%
Function 109553517.8%
Function 1174017.5%
Function 124124716.6%
Function 13106714.9%
Function 146648913.5%
Function 151813613.2%
Function 161814112.8%
Function 17119311.8%
Function 181110810.2%
Function 193029810.1%
Function 20141499.4%
Function 21283138.9%
Function 223348.8%
Function 23303748.0%
Function 24344287.9%
Function 256896.7%
Function 26588676.7%
Function 27122075.8%
Function 28295735.1%
Function 2961913.1%
Function 3041372.9%
Function 311382.6%
Function 3251902.6%
Function 332772.6%
Function 3473681.9%
Function 35050.0%
Function 360170.0%
Function 370110.0%
Function 38070.0%
Total789727210.8%

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

asdasd.PNG

 

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.

Thanks, Frank, Greg for all your help! 

 

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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)

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

Capture.PNG

 

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.

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

Hi @Forrestgump

 

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

 

222.PNG

 

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.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors