Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Average line

Hello,   Within a line chart in Power BI you have the ability to add an Average Line. I am interested to know how I would calculate this within a measure. I am looking to be able to display average...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    You could refer to below DAX formula to calculate the Average measure:

    Average =
CALCULATE ( SUM ( Table_Name[expenses value] ), ALL ( Table_Name ) )
    / CALCULATE ( DISTINCTCOUNT ( Table_Name[Month column] ), ALL ( Table_Name ) )

    Regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi Anonymous,

 

An average line is static once it is created. It is not possible to dynamically change it based on different period of time.

 

One workaround is to create measures instead of using the built-in average line. You can dynamically change the average values displayed in line chart according to the slicer selection.

 

For example, you should create a measure [Average1] which returns the average expenses for each month. And you need to create another measure [Average2] which returns the average expenses for the previous 3 months. 

 

Create a new table 'Table1' with only one column [Column1], detailed values are Average1, Average2. Add this column into a slicer.

 

Create a new measure [Average]= IF(MAXX(Table1,Table1[Column1])="Average1",[Average1],[Average 2]). Add this measure into the values section of the line chart.

 

Regards,
Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thank you for your response this could be very helpful as I build out my reporting.

     

    What I was mainly trying to determine is how to write a measure that would do an Average calculation the same as the Average on the analytics section of the line graph. Everytime I try to do an Average measure and place it into my visual, the average is being calculated based on the time frame I have selected, which ends up making it the same as the actual expeneses. 

     

    To start, I would like to get the average expenses by month where we have expenses. If we spent $25K over 5 months our average line would be $5K per month where as the actuals each month might vary from this.

     

    Thanks,

     

    Robert

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

       

      You could refer to below DAX formula to calculate the Average measure:

      Average =
CALCULATE ( SUM ( Table_Name[expenses value] ), ALL ( Table_Name ) )
    / CALCULATE ( DISTINCTCOUNT ( Table_Name[Month column] ), ALL ( Table_Name ) )

      Regards,
      Yuliana Gu