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

Helper V

## 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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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 V

@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``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.