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

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