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.
Hi,
I need my measure to calculate the average of the fullQuarter eventhough i am displaying the measure for every date of the month.
I have contract sold on every day of the month and when I display the daily row data I want to also display the average of the corresponding quarter . eg Contract 'ABC' sold on the Feb 15th shows as say $500 , and let the average of the corresponding Quarter is $450. I created a measure with 'AllExcept ' so it will keep the context of the measure still in the quarter level event thought my measure needs to be displayed on a daily row basis.
Date | Contract ID | Sold Price | QuarterlyAverage
02/15/2019 | ABC | $500 | $450
02/16/2019 | JKL | $200 | $450
02/17/2019 | POI | $250 | $450
_mQuarterlyAverage =CALCULATE( AVERAGE( TBL[Sold Price])
,ALLEXCEPT(TBL,'Date'[YearQuarter])
,TBL[statuscodename]= "Closed"
,TBL[StatecodeName]="Active"
)
YearQuarter represent the corresponding quarter of each date eg:2018 Q1, 2018 Q2 etc.
my measure keep calculating average for the full available year as oppose to the corresponding quarter.
please help me find what i am doing wrong. Thanks, JS
Hello
have you been able to solve the problem with the replies given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Hello @Puthenveedu
Try to use this measure. This should do the trick
Average Quarter =
var EOQuarter = ENDOFQUARTER(TBL[Date])
var SOQuarter = STARTOFQUARTER(TBL[Date])
return
CALCULATE(
AVERAGE(TBL[Sold Price]);
filter(
all(TBL);
TBL[Date]>=SOQuarter&&TBL[Date]<=EOQuarter
)
)
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi,
Is this maybe a solution:
Created a 2 measures:
For me its works in both a Matrix and a Table
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
In short, you will need a measure that probably involves an ALL in order to snap it out of the current monthly context and then you would FILTER that down to just the quarter you are in.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |