Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create an Constant Average Line across months

Hi,

 

I'm currently creating a 6 months report template on PowerBI.  I used a Clustered Column Chart, and went to the Analytics Pane to include an Average Line which looks like the following:

However, the value of the Average Line (8.03) is wrong, as it takes the average of the months instead of across each respondent. The correct Average of all respondents of these 6 months should instead be 7.95

 

Hence, I thought that a DAX formula will be more suitable in my case. However, after creating a measure with the folowing formula: Average = AVERAGE('(SL) 201 Mgr'[Value]), the chart that I end up with looks like this:

 

I would like a straight line with the average of all respondents across these 6-months to be in the chart.

 

Please also note that as this PowerBI chart is to be used as a template in the years to come, suggestions which requires manual input of values are less desireable. In addition, using DIVIDE, SUM, COUNTROWS, is also less desireable as my dataset will have a record of all respondents from start to end, and not all data within my dataset will be used in the reports to come.

 

Thanks for reading and helping! 🙂 

Need Help

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

    Please correct me if I wrongly understood your question.

    Please try the below measure.

     

    Average =
    CALCULATE (
    AVERAGE ( '(SL) 201 Mgr'[Value] ),
    ALLSELECTED ( [your x-axis column] )
    )

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Jihwan,

     

    Thank you so much for your help!

     

    My x-axis column is a date variable, and the chart that I have was drilled-down by Year & Month. Hence, I used the following formula:

    Average =
    CALCULATE (
    AVERAGE ( '(SL) 201 Mgr'[Value] ),
    ALLSELECTED ('(SL) 201 Mgr'[Date of Run].[Month])
    )

     

    I have tried using the suggested formula, but I ended up with a line showing the average of each month.

     

    Was looking for straight line average across the bar chart with the value of 7.95. The value 7.95 is the average across all respondents in the 6 months.

     

    Thanks again for helping! 🙂

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, Anonymous 

      Thank you for your feedback.

      if it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

       

      Thanks.

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      HI Anonymous,

      I'd like to suggest you use raw date fields instead of the time hierarchy fields that analysis and generated by power bi auto date/time features.

      Average =
CALCULATE (
    AVERAGE ( '(SL) 201 Mgr'[Value] ),
    ALLSELECTED ( '(SL) 201 Mgr' ),
    VALUES ( '(SL) 201 Mgr'[Date of Run] )
)

      If this also not help, can you please share some dummy data to test?

      How to Get Your Question Answered Quickly

      Regards,

      Xiaoxin Sheng

  • AnandKrishna's avatar
    AnandKrishna
    Regular Visitor
    3 years ago
    Try this one! Worked for me
     
    Average = CALCULATE(AVERAGE(Table[column_name]),ALL(Table_Name)
     
    NOTE: Change the column and Table name that you want to change.