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.
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.
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?
By the way, another variant of formula here
could be
=SUM(INDEX(Home_Loan[Interest Paid],SEQUENCE(12,1,(J16-1)*12)+1))SergeiBaklan Thanks a lot. I think my issue is resolved now. Than you. btw, I want to learn DAX, What is the best source or channel to go about. Pl suggest.
Thanks
Sandip.
sandipgumtya365 , you are welcome, glad to help
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.
sandipgumtya365 There are several ways to achieve this:
1) Manual method on Table
2) Manual method on Pivot
3) Automated with Power Query
4) Automated with VBA
All but #4 requires Data > Refresh All
To get my answer:
1) Close this question and mark as answered by me2) Select the method you prefer
3) Ask that question
4) Email me at mailto:Craig_Hatmaker@Yahoo.com to make sure I see your question
Today is a holiday so I'll be checking email infrequently. Our time zones are probably different. I'm on the east coast of the US so it is morning here now and will be up for the next 12 hours.