cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Proud to be a Super User!

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.