cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jaydpie
Helper IV
Helper IV

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.
pic1.JPG
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??
pic2.JPG
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??
avgx.JPG
 
Any thoughts?
J
1 ACCEPTED SOLUTION

@Jaydpie 

 

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
🙂

View solution in original post

17 REPLIES 17
nandukrishnavs
Community Champion
Community Champion

@Jaydpie 

 

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
🙂

 

@nandukrishnavs 

 

Hey,

 

Tried. Yup they are all showing last day of month.

@Jaydpie 

Try this 

 

Water TRIFR 12MMA =

AVERAGEX (

    DATESINPERIOD (

        'Calendar'[Date].[Date],

        LASTDATE ( 'Calendar'[Date].[Date] ),

        -12,

        MONTH

    ),

    [Water TRIFR]

)



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

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/109...

 

@nandukrishnavs 

 

Hey,

 

Nope, that makes the graph go blank

@Jaydpie  Can you prepare a sample dataset with the same issue and share it here?

@nandukrishnavs 

@AllisonKennedy 

 

I think I am going to throw my laptop into the air like a disc and shoot it!!

 

Here are the components. each in a table. A,B & C Individually are all correct but "D"

ABCD.jpgTable A is just the COUNT formula from my previous post looking for "injury". This is all correct

Table B is a fixed amount that never changes. So again correct

Table C is ... (Table A x 1,000,000) / Table B. So if I do a quick manual check for Jul 19... (3 x 1,000,000) / 96,000 = 31.25

So with rounding Table C July 19 = 31.25 which is correct

 

Now Table D is the AVERAGEX formula using TABLE C as the data.. What the points SHOULD be is.

Jul 19 - (0+0+0+0+0+0+0+0+0+0+0+31.25) / 12 = 2.6

Aug 19 - (Zero's + 31 + 10) / 12 = 3.4

Sept 19 - (Zero's + 31 + 10 + 42) / 12 = 6.92

Etc...

Apr 20 ( 0 + 0 + 31 + 10 + 42 + 10 + 10 + 31 + 10 + 21) / 12 = 13.75

 

So not only are the numbers incorrect but there is some odd behaviour that for 7 months its reading a constant figure??

 

 

 

@Jaydpie 

 

I think your slicer is making the problem. Because you are applying a slicer to filter the date. At the same time, you are looking for the last 12-month average. So you have to apply all() function to remove this filter. Then only you will get the previous 12 months in your context.

 



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

@nandukrishnavs 

 

Hey,

 

Where do I apply that all()?

 

I checked the filters and they are (all)

filter.JPG

@Jaydpie 

 

Try this measure.

Water TRIFR 12MMA =
AVERAGEX (
    DATESINPERIOD (
        ALL ( 'Calendar'[Date] ),
        LASTDATE ( 'Calendar'[Date] ),
        -12,
        MONTH
    ),
    [Water TRIFR]
)

If you can share the dataset, I can validate it. 



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

@nandukrishnavs 

 

I would except the data is very sensitive and contains medical and personal contact information 😞

 

There was also an error with that DAX?

TRIFR.JPG

@Jaydpie  Understood. If you can replicate 20 rows with masked data would be enough.

@nandukrishnavs 

 

If I reduce the data and remove all of the non-essential stuff to this problem it would only be

Incident #Date ReportedReport Type
11/7/19Injury
21/8/19Non-Injury
320/8/19Injury
414/9/19Injury
54/1/20Non-Injury
618/2/20Non-Injury
724/3/20Non-Injury
824/3/20Injury
92/4/20Non-Injury

 

This is example only. I didnt get exact dates from actual data

@Jaydpie 

 

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
🙂

View solution in original post

@nandukrishnavs 

 

My hero!

@nandukrishnavs 

 

How do I attach an xls file? there is no options?

I created a table and pasted the data but it re-arranged all the cells?

AllisonKennedy
Super User
Super User

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? 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!