cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## 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!:
Mastering Power BI 2nd Edition

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 III

@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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors