cancel
Showing results for
Did you mean:
Helper I

## Calculate average of last category index

Hi there,

I am trying to find a solution / measure to calculate the Average of only the last "Date Value Index" Values for each date. For example, the average for table below would be (140 + 190 + 123 + 190) / 4.

Has anyone done this before or could please include a link to the solution?

Thanks.

 Date Value Index Date Value Index 01/01/2022 100 1 1 01/01/2022 120 2 2 01/01/2022 140 3 3 02/01/2022 190 4 1 03/01/2022 122 5 1 03/01/2022 123 6 2 04/01/2022 190 7 1
1 ACCEPTED SOLUTION
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new measure.

``````Expected measure: =
VAR maxindex_samedate =
SUMMARIZE ( Data, Data[Date] ),
"@maxindex", CALCULATE ( MAX ( Data[Index] ) )
)
RETURN
CALCULATE (
AVERAGE ( Data[Value] ),
TREATAS ( maxindex_samedate, Data[Date], Data[Index] )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

4 REPLIES 4
Super User

Hi,

Here are 2 ways of solving the problem

Approach 1 (with a calculated column formula and a measure)

Write this calculated column formula

``To consider = Data[Date Value Index]=CALCULATE(MAX(Data[Date Value Index]),FILTER(Data,Data[Date]=EARLIER(Data[Date])))``

Write this measure

``Measure with spare column = CALCULATE(AVERAGE(Data[Value]),Data[To consider]=TRUE())``

Approach 2 (with a measure only)

``Measure without spare column = averagex(filter(SUMMARIZE(GENERATE(VALUES(Data[Date]),VALUES(Data[Date Value Index])),Data[Date],Data[Date Value Index],"A",[Date value],"B",[Max of date value index]),[A]=[B]),[Values])``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi @Jihwan_Kim that's exactly what I was after - thank you. Would you recommend having the index as a calculated column or create it within Power Query by merging two identical tables together?

Super User

Hi,

I prefer having an index column created within Power Query Editor, since Power Query Editor has the function called ADD INDEX COLUMN.

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new measure.

``````Expected measure: =
VAR maxindex_samedate =
SUMMARIZE ( Data, Data[Date] ),
"@maxindex", CALCULATE ( MAX ( Data[Index] ) )
)
RETURN
CALCULATE (
AVERAGE ( Data[Value] ),
TREATAS ( maxindex_samedate, Data[Date], Data[Index] )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements