cancel
Showing results for
Did you mean:
prl 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

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] ) 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] )

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. 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. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 228 members 2,323 guests
Recent signins:
• leed • lc_finance • aliahmad • CarmenCimpanu • PowerBi_Batman • v-zhohan • artemus • kkeall • nielsentm • jomagarcia • JParker66 • Sadiq9349 • Ali-Aati • remcodegroot 