cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niiru1
Helper V
Helper V

Calculate average of measure values datesbetween

I have two tables 1. Dimdate = a date key 'Table' = data table with PERSON_ID

 

They are not related as I have created a measure to count % attendance.

If I have calculate the table below:

Absence average.PNG

Is there a way to calculate the average of percentages for PERSON_ID = 135 for dates between DATE(2018,1,1),DATE(2018,12,31) e.g. average of (80.49% & 60% = 70.25%)

 

I've tried these but cant seem to get them to work.

 

Measure 4 = AVERAGex(VALUES('Employee Absence Full Table'[Month and Year]),[Measure 3])
 
or 
Measure 4 = CALCULATE(AVERAGex('Employee Absence Full Table',[Measure 3]),DATESBETWEEN('DimDate'[Dates],DATE(2018,01,01),DATE(2018,12,31)))
 
 
1 REPLY 1
AlB
Super User
Super User

Hi @Niiru1 

Place this measure in the visual you show, activate the row subtotals and you'll have the result in the row subtotals. For the dates, you can either add slicers or if you want it hard coded, you can use a CALCULATE wrapping the measure.

Measure_TOT = 
AVERAGEX(DISTINCT(DimDate[Month Year]),[Measure 7]) 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors