Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

Overall and detailed average

Hello,

I'm trying to display the data in a matrix the way I liked but I cannot get the expected presentation.

My report is composed by 4 slicers :

• The name of an expert
• The service
• The channel
• The year

Besides that I use a matrix with the mission type in row and the Average of fees in the values.

I also created a measure used in the matrix, named Overall Average fees, and this is the point not working as expected.

Here is the code of the measure :

= CALCULATE(AVERAGE(mission_exp[Fees closed claims]);ALL(mission_exp[Mission type detail]))

The expected behaviour is the following. I'd like to get the average for all the expert but for each Mission type. What I got with my measure is the average for all the experts and for all the mission type.

If no filters in the slicers are selected I should have the same value in Average and Overall fees.

I hope I'm clear enough and is there any solution to get the expected behaviour ?

Thank you.

1 ACCEPTED SOLUTION
Community Support

HI @Anonymous,

All function will ignore filter effect so your formula get similar result on each row.
I'd like to suggest you test with below formula if it suitable for your requirement:

Measrue =
VAR total =
CALCULATE (
AVERAGE ( 'mission_exp'[Fees closed claims] );
ALL ( 'mission_exp' )
)
RETURN
IF (
ISFILTERED ( mission_exp[Mission type detail] );
( total + AVERAGE ( 'mission_exp'[Fees closed claims] ) )
/ 2;
AVERAGE ( 'mission_exp'[Fees closed claims] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
1 REPLY 1
Community Support

HI @Anonymous,

All function will ignore filter effect so your formula get similar result on each row.
I'd like to suggest you test with below formula if it suitable for your requirement:

Measrue =
VAR total =
CALCULATE (
AVERAGE ( 'mission_exp'[Fees closed claims] );
ALL ( 'mission_exp' )
)
RETURN
IF (
ISFILTERED ( mission_exp[Mission type detail] );
( total + AVERAGE ( 'mission_exp'[Fees closed claims] ) )
/ 2;
AVERAGE ( 'mission_exp'[Fees closed claims] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors