Ι really do not like pivot tables but this was an eye opener. For example the H1 and H2 calculated items are included in the grand total calculation, so in order to get around this you can disable the grand totals and introduce a new calculated item that will be the sum of the months. Nice video
Thanks for the video excellent. How do you solve the issue with your 6 months H1 and H2 sum, of sales Always Working, the sum is doubled which is not correct.
I've the Fuel purchase/consumption Data, where Storage is an item and Can is present in Field. there is field named TYPE having multiple items such as Cash, Credit and CAN Now to get monthly Storage balance I tried reducing Storage-Can and it gives error. Storage is a item which is purchase entry as Credit type and in Type field the multiple items are actually direct consumption. is there any way to handle and generate the calculated field. using Fuel purchase/consumption report basically there are various more aspects to be worked out which at present I've to perform manually every month which consume more than a hour for just 50 to 80 entries per month
is it possible to create a new column where i add something to the net sales if they are from specific months? Ex: I want to add the number 1050 to the sales amount for the months of jan, march, sep. I tried doing if(calendar month = 'jan', netsales+1050,netsales) but its not working.
thank you for this I have been using pivot tables for a bit and they are time saving at times. I have a database of projects their budgets and costs. I know that I can create custom subtotals and custom calculated columns, but was wondering if i could add a line underneath a subtotal to do a calculation e.g. i have one budget for the whole project but several categories of expenses. So I need to show the breakdown of expenses and at the end sum of it and then a new line showing the value of surplus or deficit. Another thing I just noticed, if I added it to a data model to create a measure and check the variance, I am not able to choose custom subtotals I would prefer to do it without VBA, but if it is a must, why not
Thank you for the tutorial, quick question, the Calculate Field is grayed out and I even tried different file that I have and even there is grayed out , and it doesnt matter if i select any cell or not still same, any idea what would be the problem? is there any setting I have to turn on that I am not aware of ?
I'm trying to insert Calculated Item, "Gross Profit", calculated as Sales - Cost of sales but I'm getting an error message: "An item name is ambiguous. Another field in the PivotTable report may have an item with the same name. Use the syntax field[item]. For example, if the item is Oranges and the field is Product, you would use Product[Oranges]." What does this mean? How do I correct this error please? The table is the only sheet, other than the pivot table, in the workbook and the "Gross Profit" category does not exist anywhere else. I have no idea how to correct this. Thanks for any assistance you could provide.
Thank you for your reply@@LearnAccountingFinance . I managed to get assistance. "I suspect there is a name conflict with either Sales or Cost of Sales also used somewhere else. It's asking you to prefix Sales and Cost of Sales with the column name e.g. if the column name is Account, then it wants you to write the formula: =Account[Sales] - Account[Cost of Sales]". This worked, thankfully.
Excellent examples to show the demonstration. Clear and concise. Thank you.
Thanks. Glad you found it useful.
Ι really do not like pivot tables but this was an eye opener. For example the H1 and H2 calculated items are included in the grand total calculation, so in order to get around this you can disable the grand totals and introduce a new calculated item that will be the sum of the months. Nice video
Very well explained. Thank you so much.
Thank you Vishal!
Excellent and concise! Thank you!
Thank you. I learned a lot from your video.
Thanks James. Glad it helped.
God bless you richly my friend
Thank you and you too.
Thanks for a great presentation
Thank you Tahir. Glad you liked it.
Well explained please try to explain if the original table data has two headers how do you use it pivot table
Thanks for the video excellent. How do you solve the issue with your 6 months H1 and H2 sum, of sales Always Working, the sum is doubled which is not correct.
Thanks🥰
Hi Sir, what gonna do if I encountered an error in modification of formula on Calculated item?
Hi,
When I insert a calculated item, then in bottom % is coming wrong. Will please help me how to solve it.
I've the Fuel purchase/consumption Data,
where Storage is an item and Can is present in Field.
there is field named TYPE having multiple items such as Cash, Credit and CAN
Now to get monthly Storage balance I tried reducing Storage-Can
and it gives error.
Storage is a item which is purchase entry as Credit type
and in Type field the multiple items are actually direct consumption.
is there any way to handle and generate the calculated field.
using Fuel purchase/consumption report basically there are various more aspects to be worked out
which at present I've to perform manually every month
which consume more than a hour for just 50 to 80 entries per month
is it possible to create a new column where i add something to the net sales if they are from specific months? Ex: I want to add the number 1050 to the sales amount for the months of jan, march, sep. I tried doing if(calendar month = 'jan', netsales+1050,netsales) but its not working.
if you show totals on always working CO, can I use calculated fields in this formula: gross sales January / gross sales at always working CO total?
thank you for this
I have been using pivot tables for a bit and they are time saving at times.
I have a database of projects their budgets and costs. I know that I can create custom subtotals and custom calculated columns, but was wondering if i could add a line underneath a subtotal to do a calculation e.g. i have one budget for the whole project but several categories of expenses. So I need to show the breakdown of expenses and at the end sum of it and then a new line showing the value of surplus or deficit.
Another thing I just noticed, if I added it to a data model to create a measure and check the variance, I am not able to choose custom subtotals
I would prefer to do it without VBA, but if it is a must, why not
when doing this pivot for multiple tabs, the calculated field options doesn't work anymore, please help
very good
Thanks Media Tech - 53
Thank you for the tutorial, quick question, the Calculate Field is grayed out and I even tried different file that I have and even there is grayed out , and it doesnt matter if i select any cell or not still same, any idea what would be the problem? is there any setting I have to turn on that I am not aware of ?
you can't use calculated fields or items if your pivot table is sourced directly from the data model.
Amezing
Please put practiced file in the description
Hello Ubaidillah .. the link is available in the description. Here is the link again ...bit.ly/3uDFmcx
I'm trying to insert Calculated Item, "Gross Profit", calculated as Sales - Cost of sales but I'm getting an error message: "An item name is ambiguous. Another field in the PivotTable report may have an item with the same name. Use the syntax field[item]. For example, if the item is Oranges and the field is Product, you would use Product[Oranges]." What does this mean? How do I correct this error please? The table is the only sheet, other than the pivot table, in the workbook and the "Gross Profit" category does not exist anywhere else. I have no idea how to correct this. Thanks for any assistance you could provide.
Try naming the calculated item differently, eg "GP" instead of Gross Profit
Thank you for your reply@@LearnAccountingFinance . I managed to get assistance. "I suspect there is a name conflict with either Sales or Cost of Sales also used somewhere else. It's asking you to prefix Sales and Cost of Sales with the column name e.g. if the column name is Account, then it wants you to write the formula: =Account[Sales] - Account[Cost of Sales]". This worked, thankfully.
Thanks for sharing the solution.
عالی
Wow
Hi, how to change $ 4567 to $4567
Hi Gulafsha. Are you asking for a pivot table or generally in a cell?
@@LearnAccountingFinance both