cancel
Showing results 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.

Helper IV

## Incorrect average using DAX

Hi All,

I have a requirement to show the avergae of data for different protocols. Please find sample date below:

Issue: When one protocol is selected, then the values for same statuses should be aggregated, but they are getting showing as average as I have selected average for no. of days.

Expected output is 6+19=25 but it is showing as average 25/2=12.50.

The values should be aggregated for same protocols that has same statuses and values should show as average for different protocols.

Overall:

When all the protocols are selected, the result should be  (4+6+19+1)/3=10 but it shows as 7.50

Could someone please sugest on how to achieve this using DAX.
Attaching sample pbix file here.

Thank you,

Poojitha

2 ACCEPTED SOLUTIONS
Super User

@POSPOS Try this:

``````Measure =
VAR __Protocols = COUNTROWS(DISTINCT('Table'[Protocol]))
VAR __Sum = SUM('Table'[No. of days])
VAR __Result = DIVIDE(__Sum, __Protocols)
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

Hi,

Write these measures

``D = SUM('Table (2)'[No. of days])``
``Measure = AVERAGEX(VALUES('Table (2)'[Protocol]),[D])``

Drag the second one to the visual.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

Write these measures

``D = SUM('Table (2)'[No. of days])``
``Measure = AVERAGEX(VALUES('Table (2)'[Protocol]),[D])``

Drag the second one to the visual.

Hope this helps.

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

@Ashish_Mathur  - thank you for the response. this solution worked.

Super User

@POSPOS Try this:

``````Measure =
VAR __Protocols = COUNTROWS(DISTINCT('Table'[Protocol]))
VAR __Sum = SUM('Table'[No. of days])
VAR __Result = DIVIDE(__Sum, __Protocols)
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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