How To Calculate Averages per Day in Power BI w/DAX

Learn how to calculate average every day simply in Power BI with DAX. It's amazing how simple this can be if you have a date table linked to your fact table.

***** Video Details *****
0:29 - reviewing the data model
1:08 - calculating sales per day for particular customers
2:01 - using AVERAGEX and the VALUES function
3:10 - looking at different time frames
3:45 - reviewing the process
4:45 - using the same formula against a different dimension

***** Learning Power BI? *****

***** Related Links *****

***** Related Course Modules *****

***** Related Support Forum Posts *****

[EnterpriseDNA]

Related Links

Related Course Modules

Related Support Forum Posts

[sherylwu]

Thank you so much. It took me a whole afternoon to figure out how to average count by filters, until I saw your video. Save my day!

[mavrikoua]

Thank you! I applied the principle in calculating Cases Closed per day per agent and it worked beautifully...

[stephenwycherley]

Couple of hours later and I found this video. Very elegant! Should have known to come here first. Thank you.

[waldof]

This is BEAUTIFUL! Thanks for this video, thanks a lot!

[mayankhmathur]

amazing, thank you it has solved one of my recurring problem.

[diegomaldonado]

Great tutorial, got it right the first time! thanks.

[alextotheandru]

This saved me hours of work. Thank you so much!

[kullou]

Hello, Sam Thank you for the videos. I would also like you to make a video on "How to calculate Average weighted inventory cost on Power BI". Thanks you.

[rhhfhfsajajaa]

Great video, how does averagex treat blank values?

[brendonbird]

Awesome explanation of how to calculate averages. Thanks for sharing. Was a huge help to me

[RashinQuinto]

hello, how do you fix the grand total to show the overall average instead of sum? If I'm going to use a card visual, it displays the sum of the avg and not the overall avg. Thanks!

[simonmarston]

Hi Sam, thanks for the video. Could you explain to me how I would use this but to calculate the average of weekdays only, excluding weekends and any public holidays?

[barryjvr]

How do you calculate an average of "tickets received" per 30 mins over a period of 30 days?

[sonvunhu]

Hi Sam, amazing video. Just a further detail if I want to count number of stores which have average sale per day greater than 30, 000, how can I do with DAX in your example?

Thank you so much

[TristanMalherbe]

Wonderful use of DAX context transition to create a meaningful KPI - I nearly use it everyday :)

Actually, if our Dimension Table is complete, we always have this perfect equality:

AverageX ( Values ( Dim[Col] ) , [Measure] ) = [Measure] / Distinctcount( Fact[Col] )

I am currently testing these two approaches with DAX Studio to see if there are significant changes in performance - there might be since the right part of the equality doesn't evolve context transition... unless distinctcount is as 'expensive' as context transition !
Will let you know

[aqeelkasubi]

Hi, thanks for this! Gave it a go but it seemed to be summing up my value instead of averaging it. I'm using a date column in my dataset as opposed to a date dimension, not sure if this makes any difference? My date column has also automatically be given a date hierarchy.

[jr]

Great video. One question, we need a 'previsibility' for new ordes for 01 week. Let´s say that looking at sales table the 'Customer A' bought on 01/01/2018 and 21/01/2018 and 10/02/2018 so the average time to a new order to this customer is almost '20 days'. So we need review some 02 or 03 last orders to get a previsibility of 'WHEN' customer will buy again. So we know that customer A will buy again (probably/expected) in 02/03/2018. Based on that previsibility we would like to start CRM actions for cases where would be expected a new order for yesterday but not realized... There is DAX formula to check this interval based on last dates of purchase?

[antonzhong]

Thanks for the video
I have one question, if simply change the Dates[Date] to Dates[Quarter], will it average my amount per quarter ? by means total amount in 1 quarter then divide by 3 ?
Thanks

[michaelbulkley]

It looks like this formula is giving me the average per day for the days there were sales. What if I also want to include days with no sales in my average calculation. For example if day one I had 100 in sales and day two I had no sales, the current formula will calculate 100 as the average sales per day but I want a formula to calculate 50 as the average sales per day. Thanks and Thanks for the video