cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hummingbird
Helper I
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
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new measure.

 

Untitled.png

 

Expected measure: =
VAR maxindex_samedate =
    ADDCOLUMNS (
        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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Hummingbird
Helper I
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?

Hi,

Thank you for your feedback.

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.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new measure.

 

Untitled.png

 

Expected measure: =
VAR maxindex_samedate =
    ADDCOLUMNS (
        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.


Go to My LinkedIn Page


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors