Measure Not Taking Whole Quarter

[Anonymous]

The data:

 

So what this should be doing (I thought) was giving me the overall average of Quarter 1's numbers, but it is only returning the last value of the last month of quarter 1. I know the number by hand is supposed to be 87.37 if I add those 3 values and divide by 3. What can I do to get it to come out correctly?

[Anonymous]

I've been trying to figure this out for days with no correct answer and I need this figured out sooner than later. Willing to compensate at this point for whoever can help me figure this out.

[Anonymous]

If you can attach your PBIX I can write it for you

[Anonymous]

@Anonymous I really wish I could I'm hooked into a few of our internal company sites/databases which you would need access to.

[Anonymous]

Hey @Anonymous

This is a common problem that people run into and I just created a quick video on exactly this. I don't have the exact link right now but if you watch the most recent video I posted called "DAX for Power BI - True Total Sum" you will be able to acheive what you are asking for. The gist is to use the ISFILTERED function in conjunction with an X function (in your case AVERAGEX).

Link to Channel

Hope this helps,

Parker

[Anonymous]

@Anonymous the biggest thing that is hurting me is that my [retention] is a measure, not a column. I've been trying to figure out for a week how to get around this... It would be so easy if power BI just allowed people to stick both measures and columns inside of functions that only want to use columns. Because of that I feel like I cannot achieve what I'm trying to do and I'd have to throw away my whole project and ensure I'm only building columns and no measures.

[Anonymous]

I understand your point but you can definitely get around this.

Your code will be something like:

Last Quarter Retention =
IF(
   ISFILTERED(Table['Month End']),
   [Retention],
   AVERAGEX(
      Table,
      (The same code as your retention measure, maybe a slight difference for column references)
   )
)

[Anonymous]

Thank you @Anonymous I will find the video and give it a try! Desperate at this point.

[NipponSahore]

Instead of simply calculating [Retention], i would calculate Average([RETENTION])

[Anonymous]

@NipponSahore I can't do that because the [retention] is a measure, not a column. I also cannot use AVERAGEX because for some reason it returns the numbers differently. I just need a plain and simple way to somehow maybe add the numbers and divide by 3. I don't see a way to do that.