Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

AVERAGEX Problems

Hello all,

 

I am pulling my hair out with an AVERAGEX formula

Water TRIFR 12MMA =
AVERAGEX(
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-12,
MONTH
),
[Water TRIFR]
)
 
This is the formula i am using and I use it in 2 different reports, reporting on the same data but just for different parts of the business.
 
The one report produces this which is correct and the expected output.
The other report with the exact same formula produces this which is not at all the output and is strange that the first 7 months are identical??
I have also double and triple checked the data that supplies the figures for this and have isolated them and tested that they are working independantly. The pic below the line graph to the right shows me that if I evaluate things on a month by month basis that they are correct... So the graph on the left (and above) should more closely match the peaks and troughs??
 
Any thoughts?
J
Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Anonymous 

     

    Try this 

     

    Water TRIFR 12MMA =
VAR monthrange = 12
VAR result =
    DIVIDE (
        SUMX (
            DATESINPERIOD (
                'Calendar'[Date],
                LASTDATE ( 'Calendar'[Date] ),
                -1 * monthrange,
                MONTH
            ),
            [Water TRIFR]
        ),
        monthrange,
        BLANK ()
    )
RETURN
    result

     



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

17 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    6 years ago

    I'm not 100% sure how the graph on the bottom right relates to the AVERAGEX measure in question? 

     

    What is the DAX formula for [Water TRIFR] and what does that Measure look like when plotted against the same time period in the same report and page as the graph on the bottom left? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      AllisonKennedy 

       

      Hey,

       

      So the graph on the bottom right is a DAX formula that looks through a data set and counts the number of entries that have the correct value. Here is the DAX for that

      CAMMS Total Injury = CALCULATE(COUNT('CAMMS Incidents'[Report Type]),'CAMMS Incidents'[Report Type] IN {"Injury"})
       
      So I use that to test to see a simple count each month, I then went back to the data and did a manual physical count to see if it matched (which it does)
       
      I then take that amount and put it into an industry specific formula that shows a "frequency rate"
      Water TRIFR = ([CAMMS Total Injury]*1000000) / ([Water Hours / Month])
      This forumla shows me for every million hours worked how many injuries do we have. 
       
      I then plot that into my 12MMA formula from before.
  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    Anonymous 

     

    Create another DAX measure and add that in the tooltip.

    LastDateValue=LASTDATE ( 'Calendar'[Date] )

     Then verify its value in both visuals. Maybe it is creating issue.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂