Data not calculating or totalling correctly

[AmBigsPBI]

Here is a screen shot of my table. The Life Expected is a datediff formula with the Days option, divided by 365 for years. The Life Span is another datediff formula with the Days option, divided by 365 for years. The Prepayment Speed is simply the divide formula with life expected divided by life span. If you go accross the rows and divide Life Expected by Life Span the Prepayment Speed % is close but not quite correct for these. Also, the total average column field isn't calculating correctly for the division being done either. 

When I exported this table to excel to verify the data, the column average totals came out to 3.43 and 2.42, so again similar to what PBI is showing but not quite the same. And again, 3.43/2.42 is 141.74%, but doing the division in excel and then averaging that result column comes to 220.48% column total average. Is there something I'm not understanding mathematically? Wouldn't the column total average be the same both ways by calculation on column total row and by average of value rows?

Thanks

[speedramps]

It sounds like a common novice mistake.

Power BI automatically calculates measure based on the output context rather than the input context.

So if you have multiple rows then totals wont make sense, expcecially on the grand totals.

A common solutution is to use the SUMX comman which is an itterator  and allow you to control when and how totals are derived.

Learn how here https://learn.microsoft.com/en-us/dax/sumx-function-dax

and here https://www.youtube.com/watch?v=xoSKuIyPlmw

https://learn.microsoft.com/en-us/dax/sumx-function-dax

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

and here 

[AmBigsPBI]

Hello, thanks for the reply. 

I'm still not understanding, what do you mean input context vs output context? I am using calculating columns, not measures, not sure if that makes a difference.

My columns are Life Expected, Life Span, Prepayment Ratio. At the root, the columns are Start Date, End Date, Terminated Date. Life Expected is Datediff(Start Date, End Date, DAY)/365, Life Span is Datediff(Start Date, Terminated Date, DAY)/365. Prepayment Ratio is Life Expected / Life Span.

There are no sums required in the above, so how would I use SumX in this case?

And regardless of column total averages, I still don't understand why the Prepayment Ratio column values would not be the correct result of Life Expected / Life Span. 

Thanks!