cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsimcoe Regular Visitor
Regular Visitor

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 expenses for different periods of time via cards in a report. For example, for this specific project, I would want to be able to have a card that showed the average expenses for each month in this project was the $32,269.69 reference below.  I would probably then expand this to the average expenses for the previous 3 months, etc, etc.

 

The fields I am using are a calculated fiscal year column, fiscal month column, a measure for total expenses.

 

Capture.PNG

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Average line

Hi @rsimcoe,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
v-yulgu-msft Super Contributor
Super Contributor

Re: Average line

Hi @rsimcoe,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rsimcoe Regular Visitor
Regular Visitor

Re: Average line

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 Super Contributor
Super Contributor

Re: Average line

Hi @rsimcoe,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
v-yulgu-msft Super Contributor
Super Contributor

Re: Average line

Hi @rsimcoe,

 

Have you achieved your requirement with my original advice? If so, please kindly mark the corresponding reply as an answer so that others having similar concern can find the solution more easily. If you still have any question, please feel free to ask. Thanks for your understanding.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 217 members 2,117 guests
Please welcome our newest community members: