Forum Discussion
Need help with Tables
Creating PivotTable add data to data model and instead of direct aggregation (sum) use measures like
Paid Interest:= VAR total=SUM(Home_Loan[Interest Paid]) VAR Result=IF(total=0,BLANK(),total) RETURN ResultResult will be like
As a source I took the table to the left. As a comment, please don't add totals manually, Table support total row you may switch on/off and use the same formulas. Otherwise that could be side effect.
That's like
=SUMPRODUCT((HomeLoan[[Months]:[Months]]>0)*(HomeLoan[[Months]:[Months]]<=I1*12)*HomeLoan[[Interest Paid]:[Interest Paid]])
SergeiBaklan
I guess we should point out that there are major differences in the results, The SUMPRODUCT() versions are calculating cumulative results. The SUMIFS() version is calculating for each year. So for comparison, the cumulative version of SUMIFS would be:
=SUMIFS(HomeLoan[Interest Paid],HomeLoan[Months], "<=" & E1*12 )
Again, while SUMPRODUCT can also be simplified and work, SUMIFS, in my opinion, better describes the calculation - it is "Summing" numbers "If" they match criteria. I feel it is a best practice to let formulas self-document as best we can.
I'd also highly recommend using PivotTables instead of formulas. Pivots are much easier and to which we can effortlessly add timeline slicers and charts if we so choose.
Craig, perhaps you are right with business logic, I have no idea. I only answered on question how to keep structured references unchanged ("absolute") if drag the formula in E2 to the right. Assuming in E2 is correct formula.
SergeiBaklan The formula in E2 is right but in E3, a slight modification is required and then can be dragged. As it is there is your soln, is it the way for absolute reference in a table? I think, if it was not a table then it won't have been difficult. But as I would require a few more data to be added, so the table is required.
sandipgumtya365 Don't drag (also known an fill). Use copy/paste. I've used this method since 2010. It works. Just copy first cell, select other cells, then paste. Simple.
Sergei Baklan Yes - you answered his question and the structured reference you suggest is more complicated. So which is better? Complicated references with drag or simple reference with copy/paste? Or best - PIVOTTABLES! (See attached sheet Pivots).sandipgumtya365 I urge my staff to use tables properly because they save time and make models more flexible and self documenting. I also urge my staff to use PivotTables because they are absolutely awesome. Take 15 minutes to learn them. You'll love them.
