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.
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.Craig Hatmaker , SergeiBaklan Thanks.
Tried the copy+paste instead of Drag, it worked. Worked with SUMIFS too.
Now, I wanted to present the data in graphs using PIVOT. One issue is with the zero value cells. As the number of years i.e, the tenure of the loan is a variable quantity, I want the graph to adapt to it. Basically no zero values in the graph. As you can see a steep fall, is unwanted. The years in the X-axis should be as per the loan tenure. If I change it to 10, the X-axis should reflect the same. How should I do this?
If under absolute reference you mean to fix the column, when use Table1[[A]:[A]] instead of Table1[A].
If to fix the row - no, it's not with structured references. You may use cell references, OFFSET(), INDEX() depends on situation and which exactly references are required.
