Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsimcoe
Helper I
Helper I

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

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.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Employee
Employee

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.
v-yulgu-msft
Employee
Employee

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.

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.