cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ado2
Frequent Visitor

Calculate Average based on time filter selected

Hi
I have 6 months' worth of data ranging from Jan to Jun 2019 (below is a sample of the dataset). I am trying to create average measure (daily/weekly/monthly excluding weekends) for the count of IDs. Ideally these averages should change if I select different time periods. But my average measures don't seem to work well. As I select a different time, say Jan and Feb, the daily average I got is not exactly daily average between Jan and Feb. What I got is the total daily averages of Jan and Feb


Eg: Daily average of IDs count 
Jan 1000
Feb 2000 
--> daily average I want should be (1000+2000)/2 = 1500. But what I got is 3000


Capture.PNG

 

Any guidance would be appreciated!

 

Thanks

3 REPLIES 3
Community Support
Community Support

Re: Calculate Average based on time filter selected

@ado2 ,

 

You can create a measure using dax below:

Avg = CALCULATE(AVERAGE('Table'[IDs]), ALLSELECTED('Table'[Date]))

Capture.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ado2
Frequent Visitor

Re: Calculate Average based on time filter selected

Thanks for getting back @v-yuta-msft !

I want to exclude weekends from my average calculation. Will that function work in that case ? 

ado2
Frequent Visitor

Re: Calculate Average based on time filter selected

I should probably rephrase my problem to make it clearer @v-yuta-msft 

My original dataset includes transactions settled during business days only (we call it settle dates)

Following instructions from other post, I create a calendar table and build daily/weekly/monthly average measures based on the count of work days in that calendar. These measure work well if they stand alone but will break if I put in a time slicer. Will very much appreciate any help

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors