Forum Discussion

sandipgumtya365's avatar
sandipgumtya365
Brass Contributor
Nov 25, 2020
Solved

Need help with Tables

Hello EXCELers, Need some help with a formula. I was preparing a loan amortization table where I wanted to get the yearly interest paid. I have used the Sumproduct function to get the yearly data. T...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 27, 2020

    sandipgumtya365 

    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 Result

    Result 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.

sandipgumtya365's avatar
sandipgumtya365
Brass Contributor
Nov 26, 2020

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?

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 27, 2020

sandipgumtya365 

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 Result

Result 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.

Resources