DAX : Only show average sale per year on year level

[PowerRon]

Hi

this is my matrix and I only want to to show Yearly Avg Per Day on the year level.

I created two measures (see below), but both don't give the desired result. What am I doing wrong

Yearly Avg Per Day 2 =

IF(

    --ISINSCOPE('Calendar'[Date].[Year]),

    ISFILTERED('Calendar'[Date].[Year]),

AVERAGEX(

    CALCULATETABLE(

        VALUES('Calendar'[Date]),

        ALL('Calendar'        ),

        'Calendar'[CalendarYear]=MAX('Calendar'[CalendarYear])

    ),

    [Total Sales]

),

BLANK()

)

Yearly Avg Per Day 3 =

VAR _average =

AVERAGEX(

    CALCULATETABLE(

        VALUES('Calendar'[Date]),

        ALL('Calendar'        ),

        'Calendar'[CalendarYear]=MAX('Calendar'[CalendarYear])

    ),

    [Total Sales]

)

RETURN

SWITCH(TRUE(),

ISINSCOPE('Calendar'[Date].[Year]),_average,

BLANK()

)

Regards

@v-yueyunzh-msft  , do you have any idea?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Sales avg per year: =
VAR _currentyear =
    MAX ( 'Calendar'[Year] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Calendar'[Month] ), BLANK (),
        ISINSCOPE ( 'Calendar'[Qtr] ), BLANK (),
        ISINSCOPE ( 'Calendar'[Year] ),
            AVERAGEX (
                ADDCOLUMNS (
                    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear ),
                    "@sales", CALCULATE ( SUM ( Data[Sales] ) )
                ),
                [@sales]
            )
    )

[PowerRon]

This works @Jihwan_Kim 

Thank you very much.

Regards
Ron