Forum Discussion

thiamhuat2026's avatar
thiamhuat2026
Icon for Helper III rankHelper III
4 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
    4 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

alish_b's avatar
alish_b
Icon for Super User rankSuper User
4 months ago

Hi thiamhuat2026 ,

 

You could create a measure that calculates the average value and then pass that measure to the 'Value' in Y-Axis Constant Line using the fx button and in the Field value , select the measure in the 'What field should we base this on?' section.

Hope it helps!

  • thiamhuat2026's avatar
    thiamhuat2026
    Icon for Helper III rankHelper III
    4 months ago

    I am not sure if there is any bug in this, I select what I want, and there is no average or whatsoever, then it does not appear at all.

    • thiamhuat2026's avatar
      thiamhuat2026
      Icon for Helper III rankHelper III
      4 months ago

      My barchart is based on the Measure = EDU_NoCarpark (%), and I want to display the average value (horizontal line) of all those values on the bar chart. Thanks.

  • thiamhuat2026's avatar
    thiamhuat2026
    Icon for Helper III rankHelper III
    4 months ago

    I should say mine is a bit tricky.. those values on each individual bar chart is based on some measure 

    EDU_NoCarpark (%) =
    DIVIDE(COUNT('tc_merged_gold'[CASE_ID]),SELECTEDVALUE('tc_merged_gold'[EDU_without_Carpark])) * 100, but I cannot find the create another measure of the average of EDU_NoCarpark (%)
    • stoic-harsh's avatar
      stoic-harsh
      Icon for Super User rankSuper User
      4 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
        4 months ago

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

        Best,

        Harshit