you solved a puzzle for me that I spent many days searching for, thanks
@rexbk
3 ай бұрын
Thank you for this useful presentation.
@computertutoring
3 ай бұрын
Thanks my friend really appreciate it.
@eamonnwarters4282
2 ай бұрын
Excellent tutorial
@computertutoring
Ай бұрын
thanks
@SeanSmithPPC
Жыл бұрын
Great tutorial...only had to watch it once and I'm good to go. Thank you.
@computertutoring
8 ай бұрын
that's great
@cindycubbin2932
3 жыл бұрын
Thanks so much - really clear.
@computertutoring
3 жыл бұрын
Really pleased.
@puppy68
2 жыл бұрын
Very useful for me.. Next day I gonna use this field in my report 💪
@computertutoring
2 жыл бұрын
Cool 😎 You'll so impress your colleagues
@praveenk3517
2 жыл бұрын
I am PRAVEEN. Thanks sir ,that's use full to me and many more doubts in Excel please rectified all those things one by one I'll contact u from India , tamil nadu
@caraentz7787
2 жыл бұрын
Well done so easy to understand!!
@computertutoring
2 жыл бұрын
Really glad this helped you.
@alinbajracharya795
Жыл бұрын
Very direct and useful.
@computertutoring
Жыл бұрын
Thanks
@pejca88
3 жыл бұрын
Nice, looking forward to conditional formating for pivot tables.
@computertutoring
3 жыл бұрын
Thanks for saying will try and get that out this week.
@gabriellacollin6232
5 ай бұрын
Thank you!
@computertutoring
4 ай бұрын
Thanks - appreciate it
@sheiladillon9022
3 жыл бұрын
Really helpful - thanks
@computertutoring
3 жыл бұрын
Really pleased it helped!
@sargentomor4019
2 жыл бұрын
Well done. Exactly what i needed
@computertutoring
2 жыл бұрын
Coolio! Great to hear. 😀
@cotua
Жыл бұрын
Great video! To the point and well made.
@Alex-sw9rj
10 ай бұрын
Thanks mate!
@jeffjames7774
2 жыл бұрын
great job - thank you so much
@computertutoring
2 жыл бұрын
Thx
@sathishkumar9880
Жыл бұрын
Thanks
@computertutoring
Жыл бұрын
Glad the Excel Tutorial helped
@amanuppal294
Жыл бұрын
Nice, very Nice!!!
@computertutoring
Жыл бұрын
Thanks
@srodgers66
4 ай бұрын
I have a field which contains "Yes" or "No" values. I want to create a calculated field that shows the % of Yes values against the overall total. Is that possible?
@computertutoring
4 ай бұрын
I would tend to write calculations like this through PowerPivot and a measure. I would use the Filter or calculate function something like this: =DIVIDE( CALCULATE(COUNTROWS(Table1), Table1[Response] = "Yes"), CALCULATE(COUNTROWS(Table1), Table1[Response] = "No"), 0 )
@srodgers66
4 ай бұрын
@@computertutoring I appreciate you taking the time to reply!
@user-dx3lk4he6z
10 ай бұрын
Hi! I am trying to do a more complicated (I think) Pivot Table analysis but I'm quite stuck...I have a raw spreadsheet that has a list of projects by year they were started and year they will close. I'm trying to create a running 'active' list by year to calculate different things like revenue. In a basic table I was able to do this by using a formula that sums all projects that opened on/before the year in question and closed on/after the same year in question (i.e. in year 2018 I had a list of projects that had been opened before or in the year 2018 AND had not closed before 2018; in 2019 if any projects had closed in 2018 they would be omitted from the list but any new projects that had been added in 2019 would now be added to the list). I would like to create a dashboard with this data and so need to create pivot tables. Is there a way to put a more complicated formula into Pivot Table/any suggestions on how to translate this into a pivot table?
@w.manders2817
7 ай бұрын
how to use it on pivot columsn which aren't headers in the datatab?
@computertutoring
7 ай бұрын
If you want to use it for data that's not in the original data tab you will have to use Power Pivot and create a relationship between the two columns
@gautamdeusa
Жыл бұрын
Thanks. Learnt something new. But I am trying to insert a calculated field "month" from one data column. When I do that with "Calculated field", its summing that up too. I want just to have the month calculated from date column and add that on the X axis.
@computertutoring
Жыл бұрын
You usually add that column as a row or a column in the pivot table and apply a filter, this way you wouldn't need to add a calculated field.
@JeffreyDanielsHamid
Жыл бұрын
On my pivot table I've got the 'average of budget' and 'average of revenue'. It's just simply subtracting the two will give me the average profit. However, every time I use calculated fields, it doesn't give me the correct answer. Why is that?
@gmo709
2 жыл бұрын
Trying to figure out how to edit a formula for a calculated field i created. Hope this has that info. Anyone know how to edit the calculation???
@AG-ng4gn
Жыл бұрын
I am also struggling with the same issue, did you know how to edit the formula in Pivot calculated field?
@peculiarfilm
6 ай бұрын
Can we filter in calculated field?
@computertutoring
6 ай бұрын
Yes wasn’t sure but just did it
@rajeshganapathi7924
2 жыл бұрын
great bro
@computertutoring
2 жыл бұрын
My Pleasure 😊😊
@jessemozingo1279
2 жыл бұрын
I appear to be working in the same version of Excel as in this video, on a Pivot Table that has a similar setup (Data in both Rows and Values). I'm trying to create my calculated field using a field in Rows and a field in Values, but the calculated field won't pull data from the proper columns. Field names are correct. Any insight?
@computertutoring
2 жыл бұрын
🤔 could check the field names the you're using in the calculation are the same as the source data.
@arifahaddison3899
Жыл бұрын
This only uses the orginal fields, how do I use the aggregated fields within the table. I have a count and a sum I want to divide it by.
@onderkokumer9438
2 жыл бұрын
calculated field is not activated (selectable) if we use power pivot (consolidated tables in the pivot)
@computertutoring
2 жыл бұрын
Not exactly sure but if you're using Power Pivot best to look at creating "Measures"
@24cece24
2 жыл бұрын
Hi. Is it possible to create a calculated field column based off another already calculated field column in a pivot table? I've created two columns in a pivot table - each ranking a certain measure (eg sales figures). I now want to create another column in the pivot table which would be an average of those two rankings columns. thanks for your help
@computertutoring
2 жыл бұрын
Good question, yes you can but I only know how to do this using Power Pivot the steps would go like this: 1) Add the table to the data model, 2) Create 2 Measures to SUM the Received and Expenses. "TotalRec:=SUM(SalesData[Received])" & "TotalExp:=SUM(SalesData[Expense])". You would add the measures in the grid at the bottom of the screen in PowerPoint. 3) Then, create a both the ranking formulas: "ReceivedRank:=RANKX(ALL(SalesData[Description]),[TotalRec])" & "ExpenseRank:=RANKX(ALL(SalesData[Description]),[TotalExp])". Add them in the same place at the bottom. Finally you can create the Average Ranking Formula: "AvgRank:=([ReceivedRank]+[ExpenseRank])/2". Which will give you the average. I know it's a bit much and if anyone has a better solution I welcome. Make sure you're subscribed to our website as I will update the solution with pictures on the weblinke: www.computertutoring.co.uk/excel-tutorials/accounts-excel/add-calculated-field-to-pivot-table/. As soon as I get more time.
@roywilson9580
Жыл бұрын
@@computertutoring It is better to do it in 1 hit - use the keyword Var and a name then the equals sign write the first measure as you have it, then add a second Var statement and add the 2nd measure etc. for all measures required then lastly type return and sum the 2 last Var(iables) you have just created and divide by 2 (or better still enclose the sum within a Divide() statement, that way you can deal with div/0 within the calculation.
@LiziMillan
Жыл бұрын
Any idea why I am unable to add more than one calculated field to a pivot table?
@donrossbach750
2 жыл бұрын
Hi Simon: Great Video! I’m working with a list of work orders by technician that lists the number of days it took to complete each work order. In my pivot table I can get a great view of total number of work orders for each person and average days to complete, But now I’m Looking to find out what percentage of work orders by person was completed 9 days or less. Was thinking of using a countif equation but not sure where to enter it. Could you provide some tips on how to best approach this? Don
@computertutoring
2 жыл бұрын
For anything more complex I would add the orgiinal data (Accounts Data) to the data model and use PowerPivot to write a measure like the following which works out the percentage of all received above 100. Percentage of orders Rec above 100:=CALCULATE(COUNT([Received]),SalesData[Received]>100)/COUNT([Received]) So if you were doing this with the sheet used in the exercise available from the support site I would: 1 - Click the table of date 2 - Use the PowerPivot tab to load to data model 3 - In the section at the bottom enter: Percentage of orders Rec above 100:=CALCULATE(COUNT([Received]),SalesData[Received]>100)/COUNT([Received]). There might be an easier way but any more complicated calculated fields I always use power pivot. Hope this gives you some direction. 😁
@donrossbach750
2 жыл бұрын
@@computertutoring Thank you very much for the fast response. I will give this a try
@sanjaykhadilkar1380
2 жыл бұрын
Is it possible to insert calculated field in pivot chart
@computertutoring
2 жыл бұрын
Yes! As pivot charts are based on pivot tables. So make up the pivot table with all the fields you need, then create a chart from it.
@ozzythepolar
2 жыл бұрын
Thank you for this video. How can we get the percentage of the same column total if there is a seperation on another column basis ( ex. received amount represents in one column but there is a seperation either it is partially or fully received on another column as a text only on the main table which is feeding the pivot )
@computertutoring
2 жыл бұрын
If I understand from your question that you would like a text field appearing as values in a pivot table you would have to do this in PowerPivot. I take it you've already tried all the options on the Show Values As tab in value field settings? Sorry this isn't the answer you're looking for. Check out the following: kzitem.info/news/bejne/mJWP3IOmo4dzgYY
@ozzythepolar
2 жыл бұрын
@@computertutoring I think I should ask it in better way , this is not the answer that I am looking for but I found it in another video. BTW the link that you have provided is also something new for me which is very usefull ; thanks for it and keep up the good work !
@computertutoring
2 жыл бұрын
@@ozzythepolar Glad you found the answer, wonderful feeling isn't it 😁
@ozzythepolar
2 жыл бұрын
@@computertutoring for sure, on my way for the next challange already ;)
@cjsaun14
2 жыл бұрын
Calculated field is greyed out. How can I change my data from an OLAP file? I hope I didn't butcher the lingo.
@kipronobitok
Жыл бұрын
How do l calculate 2™% of an invoice total on pivot calculate field
@jawaddatusapalao3411
2 жыл бұрын
I try this formula is not showing percentage my screen excel showing 0.00% & #DIV
@computertutoring
2 жыл бұрын
Make sure that the names of you fields are correct. Paste in the formula you got
Пікірлер: 73