Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
2 years ago
Solved

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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    POSPOS Try this:

    Measure = 
  VAR __Protocols = COUNTROWS(DISTINCT('Table'[Protocol]))
  VAR __Sum = SUM('Table'[No. of days])
  VAR __Result = DIVIDE(__Sum, __Protocols)
RETURN
  __Result
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    POSPOS Try this:

    Measure = 
  VAR __Protocols = COUNTROWS(DISTINCT('Table'[Protocol]))
  VAR __Sum = SUM('Table'[No. of days])
  VAR __Result = DIVIDE(__Sum, __Protocols)
RETURN
  __Result
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.