Dax for overall average of last value in every groups

[tamara_nsb]

Hi Guys, 

I have the following data and I need to display the Average Value of items using only the most recent input for each item and grouped by sortOrder.    

Date	ItemID	SortOrder	Value
8/1/2020	403	5	1.77
8/1/2020	403	1	1.77
8/1/2020	429	2	6.1
8/1/2020	429	3	11.03
7/1/2020	220	4	34.23
7/1/2020	403	1	1.60
7/1/2020	415	4	7.87
7/1/2020	429	2	4.97
7/1/2020	429	3	33.03
7/1/2020	123	2	18.17
7/1/2020	123	3	27
7/1/2020	123	4	50.03

Expected Results:    I am able to get the count of items with Count ItemID (Distinct)

Sort Order	Average of Value	Number Of Items
1	1.77	1
2	12.135	2
3	19.015	2
4	30.71	3
5	1.77	1

Please let me know if more information is needed,  I am really stumped.

I can create a measure that will return the last value for each group,  but then how do i get that measure to average by Sort Order

[tamara_nsb]

Thanks for your response, that worked perfectly.    I was on the right track using summarize and max(date) but I was missing the AddColumns which was the step that made the difference.

[v-yueyunzh-msft · Accepted solution]

Hi , @tamara_nsb 

Thanks for your sample data and end result first!

Here are the steps you can refer to :
We can create a measure like this:

Measure = var _t =ADDCOLUMNS(  SUMMARIZE('Table','Table'[SortOrder],'Table'[ItemID] , "Date" , CALCULATE(MAX('Table'[Date])) )    ,  "value" , var _date=[Date] return CALCULATE( SUM('Table'[Value]),'Table'[Date]=_date)  )

return

AVERAGEX(_t,[value])

Then we can put it on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly