Forum Discussion
AVERAGEX Problems
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 🙂
Anonymous Can you prepare a sample dataset with the same issue and share it here?
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"
Table 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??
Anonymous
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 🙂Anonymous
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 🙂