Find Quarterly average event though the context of the table is daily

[Puthenveedu]

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

[v-frfei-msft · Accepted 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.

[dm-p]

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