Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Normally an ITERATOR function is used to aggreagte a MEASURE.
Try this pattern
AverageofaMeasure = AVERAGEX ( VALUES ( TableName[Week] ), [CountOfObjects] )
Normally an ITERATOR function is used to aggreagte a MEASURE.
Try this pattern
AverageofaMeasure = AVERAGEX ( VALUES ( TableName[Week] ), [CountOfObjects] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |