DAX formula for averages

[EMassicot]

I have a table with the columns "ID" ,"month" , "score" and "publish date". I want to first create a measure that gets me the "average score per publish date" and then use the values from the measure to get the average score per month.

Here's a table as an example

ID	Score	Publish Date
1	90	9/14/2022
1	60	9/14/2022
1	75	9/15/2022
1	85	9/15/2022

What I want but do not know how to do.

Step 1. Averge score per publish date 9/14/2022

90+60 = 150/2= 75

Step 2. Average score per publish date 9/15/2022

85+75= 160/2 = 80

Step 3. Average score per month. ( using average score per publish date to calculate)

80+75= 155/2 = 77.5

What I know how to do but do not want.

Step 1. Average score per month( using scores to caluclate)

90+60+85+75 = 77.5

[johnt75 · Accepted solution]

The below measure should work across any date range - week, month, quarter etc

Avg Score =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "@val", CALCULATE ( AVERAGE ( 'Table'[Score] ) )
    ),
    [@val]
)

[EMassicot]

I have a table with the columns "ID" ,"month" , "score" and "publish date". 

What I ultimately want, is to get the avearge score per each publish date using a DAX  formula.

Here's a table as an example

ID	Score	Publish Date	Month
1	90	9/14/2022	September
1	60	9/14/2022	September
1	75	9/15/2022	September
1	85	9/15/2022	September

What I want but do not know how to do.

Step 1. Averge score per publish date 9/14/2022

90+60 = 150/2= 75

Step 2. Average score per publish date 9/15/2022

85+75= 160/2 = 80

Here is also the DAX formula that I tried using but it's not giving me exactly what I want.

Average Score Per Publish Date =

CALCULATE(

AVERAGE ( 'Table'[Score] ),GROUPBY('Table','Table'[Publish Date], 'Table '[ID]),

[Greg_Deckler]

@EMassicot Put Publish Date in a table visual. Use a simple Average aggregation on Score.

[v-yalanwu-msft · Accepted solution]

Hi, @EMassicot ;

You could create measures as follow:

1.

average score per publish date = CALCULATE( AVERAGE('Table'[Score]),ALLEXCEPT('Table','Table'[Publish Date]))

2.

average score per month = AVERAGEX(SUMMARIZE(ALL('Table'),[Publish Date],"1",[average score per publish date]),[1])

average score per month2 = CALCULATE(AVERAGE('Table'[Score]),FILTER(ALL('Table'),EOMONTH([Publish Date],0)=EOMONTH(MAX('Table'[Publish Date]),0)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75 · Accepted solution]

The below measure should work across any date range - week, month, quarter etc

Avg Score =
AVERAGEX (
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "@val", CALCULATE ( AVERAGE ( 'Table'[Score] ) )
    ),
    [@val]
)