cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZV12
Helper III
Helper III

How to calculate "Average" data in DAX?

Dear all,

I need help to calculate the Average data YTD/MTD in DAX. Here below please the Excel chart, the formula is simple in Excel to calculate the average data since January till the month to date, but how to transform it into DAX in PBI?

 

DataJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
Rate %92,10%92,70%91%         
             
             
DateAverage           
January92,10%           
February92,40%           
March91,93%           
April            
May            
June            
July            
August            
September            
October            
November            
December            
             

 

Thanks in advance for your help.

Best regards

Rachel

1 ACCEPTED SOLUTION
selimovd
Community Champion
Community Champion

Hey @EZV12 ,

 

it would be helpful if you show the tables in Power BI and not in Excel. Like this I don't know how the data looks and can just guess how it would work.

This indicator you have to add to your measure then:

 

Average YTD Rate 1 =
CALCULATE(
    AVERAGE( myTable[myColumn] ),
    myTable[Indicators] = "Rate 1",
    DATESYTD( DateTable[Date] )
)

 

The same then for rate 2.

 

By the way, when you reply, make sure to link me @EZV12 , otherwise I will not be notified when you answer.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

4 REPLIES 4
selimovd
Community Champion
Community Champion

Hey @EZV12 ,

 

do you have a date table?

If yes, then you can ust the following function:

Average YTD =
CALCULATE(
    AVERAGE( myTable[myColumn] ),
    DATESYTD( DateTable[Date] )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hello Denis,

Thanks for your reply, however, in my data table, in one column, there are different values for different indicators. I need to calculate the cumulative average data YTD for each indicator. Here below please find a sample chart: 

How to modify your DAX formula accordingly? Thanks.

EZV12_0-1621968418681.png

 

selimovd
Community Champion
Community Champion

Hey @EZV12 ,

 

it would be helpful if you show the tables in Power BI and not in Excel. Like this I don't know how the data looks and can just guess how it would work.

This indicator you have to add to your measure then:

 

Average YTD Rate 1 =
CALCULATE(
    AVERAGE( myTable[myColumn] ),
    myTable[Indicators] = "Rate 1",
    DATESYTD( DateTable[Date] )
)

 

The same then for rate 2.

 

By the way, when you reply, make sure to link me @EZV12 , otherwise I will not be notified when you answer.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Hello Denis,

It works! Many thanks!

Best regards

Rachel

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors