cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lailailaieric
Regular Visitor

Average Line across the Bar Chart

Hiii. 

 

As I am trying to draw an average line for my bar chart, the numbers showed up as the average of the place themselves instead of the average as a whole. Can anyone solve this? Below would be the diagram for illustration. 

 

Line Chart.JPG

Thanks a lot. 

 

11 REPLIES 11
v-huizhn-msft
Microsoft
Microsoft

Hi @lailailaieric,

Have you resolved your problem? If it did, I am glad to hear that, and please mark the helpful reply as answer,so that more people can find workwroud form here. Thanks for understanding.

Best Regards,
Angelia

Hi @v-huizhn-msft

 

Using DAX, I found that I am needing help on the same matter. Here is one of the lines on the char

 

SchedLagMedian = MEDIAN('Locations'[Important_Column])

Here is another line. 

 

 

Month/Year = FORMAT(DATE(LEFT('Locations'[Months], 4), RIGHT('Locations'[Months], 2), 1), "MMM-YY")

lastly, here is the horizontle line like what I am trying to replicate dynamically

 

 

LagTarget = 14

Essentially I need a horizontle line like that above but referenced from another chart.  That code is here: 

 

 

Summarized Lag Table = 
UNION((ADDCOLUMNS(SUMMARIZE('Locations',Locations[Areas],"Value", 14,"Target", BLANK()
    ),"Type", "Goal", "Sort", 1 )),
CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Locations',Locations[Areas]), 
        "Value", CALCULATE(MEDIAN('Something'[Important_Column])),
        "Target", BLANK(),
        "Type", "Performance",
        "Sort", 2 ),
    'Something'[Time] = 1 ),
ADDCOLUMNS(SUMMARIZE(Locations, Locations[Areas]),
        "Value", SUMMARIZE('Lag Medians', "Average", AVERAGE('Lag Medians'[Value])),
        "Target", BLANK(),
        "Type", "All Locations",
        "Sort", 3))

 

 

This produces a line graph that has a flat horizontle line and a performance line.  Another line needs to exist which is the calculated median of performance.  Perhaps something like this... 

 

Median = MEDIAN(SUMMARIZE('Locations'[Important_Column])) 

 

 

But when I put that in, I simply get the same as if I added the median at each month rather than the average median.  Please help? Thanks in advance!

 

Mark

Hi @S184019,

 

Instead of the MEDIAN in the summarize, try to use the MEDIANX, this is an aggregator formula that does the median but calculating the values row by row and then returning the final result.

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey @MFelix,

 

Thank you for your super quick reply!  The difficulty might be a bit deeper than that.  I am not able to medianx of summarized('Locations'[Important_Column])) but that truly is a GREAT suggestion and point. 

 

Sincerely,

 

Mark

Hi @S184019,

 

Without some data sample is difficult to give a better respons although you have the measures you use the data is fundamental to have the right response, if you can share a sample and expected result would be easier to give you a more accurate answer.

 

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi,

 

I have designed to show average of data for whole range of years, but when selected particular bars in chart average seems to be not dynamic. As, it is shown for whole data not for specific seleced part.

 

Please suggest how to make this average line to be dynamic(based on selection in the visual itself).

MFelix
Super User
Super User

Hi @lailailaieric,

 

You are adding the Average as a measure correct? When you add a measure the calculations are based on the variables that you chose in your visuals so you are looking at the average of MEasure 2 of Singapure so this gives you the value of Singapure.

 

You need to add the analytics to your chart and then choose average in that you can also chose in wich measure you want the average and then add it, see print below any question please tell me.

 

If the average is for both measures you then need to change your DAX formula in order to achieve the total average, but to help you with that I will need further information on you data.

 

average.png

 

Regards,

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi,

 

I have done the same way as you said going to analytics pane and create an average line.

 

But the issue is the average line value on the bar and the same value when i put on single row chord is not matching.

 

Could you please help me out on the issue. Thanks.

 

Line value not matching.PNG

Hi @Anonymous,

 

Are the two visuals (bar chart and card) with the same filters applied?

 

Should work the same way, I tried it and only when making different filters or context then the results didn't match.

 

Regards.

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

HI @MFelix

 

Have applied same filters for both chord and bar but still having the same issue as data contains na, n/a, hourly, included, only twice and so on with few numbers.

 

I did validation for all the text fields using whole number as a data type but no idea from where it is taking and giving some wrong values between two charts.

 

Thanks you but if you can do help me out on this it would be a great pleasure.

 

 

Hi @Anonymous,

 

Can you share a sample of your file? If there is sensitive data maybe share it through private message or do a mockup of the data.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors