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

calculate average of calculated measure

Hello,

 

I am trying to build dashboard in PowerBI which has Calculated Measure as CountOfObjects in 4 weeks from start of week as Monday.

CountOfObjects = Calculate(Count(Objects), Filter(SampleTable, (SampleTable[date] >= Min(StartTestDate)) && (SampleTable[date] <= Max(EndTestDate))))

Here, StartTestDate = 1 Jan 2018 and EndTestDate = 11 Feb 2018

My date slicer on page filters data and, according to filtered data, I calculate CountOfObjects in selected window. As per requirement, this works fine when week duration is selected in date slicer.

But when months date window is selected, it performs rolling total. I want simple average of weekly data. How to calculate average (not moving average) of calculated measure?

My table structure is as below:

End of Week

Objects

7 Jan 2018

10

14 Jan 2018

35

21 Jan 2018

42

4 Feb 2018

5

11 Feb 2018

20

 

For week ending 7 Jan 2018, CountOfObjects is calculated as cumulative total of objects for 4 weeks ahead which is (10+35+42+5+20)=112 and so on for other dates. The result (Not stored in any table) for individual week is as below:

Week

CountOfObjects

1 Jan – 7 Jan

112

8 Jan – 14 Jan

250

15 Jan – 21 Jan

172

 

 

I am trying to calculate average as (112+250+172)/3 =178(rounded to integer). I am not able to create DAX formula to find average of calculated measure. Please help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: calculate average of calculated measure

@prl

 

Normally an ITERATOR function is used to aggreagte a MEASURE.

 

Try this pattern

 

AverageofaMeasure =
AVERAGEX ( VALUES ( TableName[Week] ), [CountOfObjects] )

View solution in original post

1 REPLY 1
Super User
Super User

Re: calculate average of calculated measure

@prl

 

Normally an ITERATOR function is used to aggreagte a MEASURE.

 

Try this pattern

 

AverageofaMeasure =
AVERAGEX ( VALUES ( TableName[Week] ), [CountOfObjects] )

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 228 members 2,323 guests
Please welcome our newest community members: