cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## MEASURE CALCULATING THE AVERAGE OF ALL THE VALUES FROM MAX DATES BY MULTPLE COLUMNS

Hi Guys,

Newbie here,

Can you help me create a measure that will calculate the average of all the last values from a certain column based on a date with a group by function?

For example.

Highlighted in YELLOW are the values from the MAX of date column based on ACCOUNT, EMPLOYEE, and TYPE.

RAW TABLE

DESIRED OUTPUT:

I hope I clearly explained what am I trying to accomplish. Let me know for any questions.

2 ACCEPTED SOLUTIONS
Community Support

Hi @Anonymous ,

First create a calculated column as below:

``````_check =
VAR _maxdate =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Employee] = EARLIER ( 'Table'[Employee] )
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
),
[Date]
)
RETURN
IF ( 'Table'[Date] = _maxdate, 1, BLANK () )
``````

Then create a measure as below:

``````Average =
VAR _sum =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Account] = MAX ( 'Table'[Account] )
&& 'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[_check] = 1
),
'Table'[Value]
)
VAR _count =
CALCULATE (
COUNT ( 'Table'[Account] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = MAX ( 'Table'[Account] )
&& 'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[_check] = 1
)
)
RETURN
DIVIDE ( _sum, _count )
``````

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Super User

Change All Function with AllSELECTED hope it will works

5 REPLIES 5
Super User

@Anonymous , Try a measure like

calculate(averageX(Table, LASTNONBLANK(Table[Date], Max(Table[Value]))), filter(allselected(Table), [ACCOUNT] = max(Table[Account]) && [EMPLOYEE] = max(Table[EMPLOYEE])
&& [TYPE] = max(Table[Type])))

Anonymous
Not applicable

Hi @amitchandak ,

Thank you for your response. However, the result is different from what I've expected. Can you check if there is something that I did wrong?

Please see the screenshot below for the result of my query.

Community Support

Hi @Anonymous ,

First create a calculated column as below:

``````_check =
VAR _maxdate =
MAXX (
FILTER (
ALL ( 'Table' ),
'Table'[Account] = EARLIER ( 'Table'[Account] )
&& 'Table'[Employee] = EARLIER ( 'Table'[Employee] )
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
),
[Date]
)
RETURN
IF ( 'Table'[Date] = _maxdate, 1, BLANK () )
``````

Then create a measure as below:

``````Average =
VAR _sum =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Account] = MAX ( 'Table'[Account] )
&& 'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[_check] = 1
),
'Table'[Value]
)
VAR _count =
CALCULATE (
COUNT ( 'Table'[Account] ),
FILTER (
ALL ( 'Table' ),
'Table'[Account] = MAX ( 'Table'[Account] )
&& 'Table'[Employee] = MAX ( 'Table'[Employee] )
&& 'Table'[_check] = 1
)
)
RETURN
DIVIDE ( _sum, _count )
``````

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Anonymous
Not applicable

Thank you for this solution. It got the correct output that I wanted.

However, what if I have a TYPE slicer into the dashboard? The result is still the same which must be only the selected filters only will be computed in the measure.

Thank you for your assistance on this.

Super User

Change All Function with AllSELECTED hope it will works

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors