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
Solved! Go to Solution.
Hi @Puthenveedu ,
Please try to update your measure as below.
_mQuarterlyAverage =
CALCULATE (
AVERAGE ( TBL[Sold Price] ),
FILTER (
ALL ( TBL ),
TBL[statuscodename] = "Closed"
|| TBL[StatecodeName] = "Active"
),
VALUES ( 'Date'[YearQuarter] )
)
If it doesn't meet your requirement, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Puthenveedu ,
Please try to update your measure as below.
_mQuarterlyAverage =
CALCULATE (
AVERAGE ( TBL[Sold Price] ),
FILTER (
ALL ( TBL ),
TBL[statuscodename] = "Closed"
|| TBL[StatecodeName] = "Active"
),
VALUES ( 'Date'[YearQuarter] )
)
If it doesn't meet your requirement, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Puthenveedu - did you try the DAX forum with this one? I suspect the answer would require using AVERAGEX with the ALLEXCEPT, rather than just AVERAGE but I think it's likely you'll get a faster and better quality answer if you post it over there.
Good luck!
Daniel
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
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 |
---|---|
17 | |
2 | |
2 | |
1 | |
1 |