Forum Discussion

thiamhuat2026's avatar
thiamhuat2026
Icon for Helper III rankHelper III
3 months ago
Solved

Average horizontal Line across the Bar chart

how to have a average horizontal line across the bar chart with the display of the average value above the line on the left side?  
Need Help
  • stoic-harsh's avatar
    stoic-harsh
    3 months ago

    Hey thiamhuat2026,

    To add a dynamic average line to your bar chart, first create this measure:

    Avg EDU NoCarpark (%) =
AVERAGEX(
    VALUES('tc_merged_gold'[TOWN_COUNCIL]),
    [EDU_NoCarpark (%)]
)

    Then, go to Visualizations > Analytics (search glass icon) > Y-axis constant line > Value (select above measure) and click ok.

    Looking at your base measure, SELECTEDVALUE() returns blank the moment more than one distinct value exists in the filter context, silently breaking the average measure. MAX() always returns a single value regardless, making your calculation reliable across all filter and slicer combinations.

    EDU_NoCarpark (%) =
DIVIDE(
    COUNT('tc_merged_gold'[CASE_ID]),
    CALCULATE(MAX('tc_merged_gold'[EDU_without_Carpark]))
) * 100

    Hope that helps.

    Best,

    Harshit

stoic-harsh's avatar
stoic-harsh
Icon for Super User rankSuper User
3 months ago

Below is the result (I took a sample data of Singapore Town Councils from internet):

Best,

Harshit

thiamhuat2026's avatar
thiamhuat2026
Icon for Helper III rankHelper III
3 months ago

can you share the link of where you get the sample data of Singapore Town Councils from internet?

  • stoic-harsh's avatar
    stoic-harsh
    Icon for Super User rankSuper User
    3 months ago

    Hey thiamhuat2026,

    First, I pulled the list of Town councils (19 or so, in count) from Wikipedia (link), then used your bar chart screenshot alongside that list to give ChatGPT and Claude more context. Claude ended up generating a cleaner dataset overall. I can also share the prompt if that's useful!

    Hope that helps.

    Best,

    Harshit