i have a database of monthly sales of a regional distributor and im using a pivot table to manipulate my data. i want to show only what account makes a positive sales for a specific product and i want to get the total number of accounts who have the positive sales. there are multiple entries in my database, the condition is if an account have positive sales of a specific product regardless on how many times the account bought the result should be 1 (meaning one buying account)
i've tried to use a calculated field in my pivot table, i get the correct result per account but when it comes to grand total its not getting the sum of the total number of accounts. here is the sample of what i need to get..
you can see that there are multiple entries but it shows only 1 per account when it has at least 1 positive sales of a specific product and 0 if it hasn't any sales/negative... and for the total it adds the number of accounts to get the total number of buying accounts..
I have a PivotTable which shows the number of packs produced in a month. I created a calculated field which shows the number of vehicles needed to move these packs. The number of packs per vehicle is dependent on two criteria, the Pack Description and the Store. My calculated field, Shunts, is described as:
Middlewich & Tall = Packs / 30 Middlewich & other = Packs / 60 Other & tall = Packs / 26 Other & other = Packs / 52
However, it doesn't work!
I know Calculated Fields can be difficult to work with, but this is crazy and I think I'm missing something obvious as I'm sure I've had IFs work like this before! I've attached my example, also showing the values I'd expect to see.
i have a problem regarding calculated field.. i have data of sales of a distributor with all the accounts that he handles.. i want to get the total number of buying accounts for that distributor..
buying account is an account that bought a specific product for at least one time... even the account buy 2 or 3 times more for that specific product the account is considered as 1 buying account..
i want to use a pivot table so i can manipulate the data.. first i tried to add an additional field to my data and put the formula "if(norbo_val>0,1,0)" where norbo_val is the total sales less the rejects. it means if ever an account have positive sales the account is considered as a buying account for that specific product. my problem is that when that account bought the same product for 2 or more times it sums all the times an account bought for that specific product..
so i decide to remove the field in my data and create a calculated field in the pivot table. i input the same formula to the calculated field and it gives me the correct result where if ever an account bought a specific product for at least one time regardless on how many times the account bought that same product, the account is considered as 1 buying account. the only problem i am encountering now is that the grand total is not summing up the total numbers of buying accounts if i list all the accounts. it seems the formula is affecting the grand total where it should add the total of buying accounts but instead using the formula of the calculated field so the result is only 1.
'Pivot Table Calculated Fields" - Below is my pivot table.
I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).
I know the following steps:
1) Click on the Pivot Table
2) Choose "Formulas"
3) Choose "Calculated Field"
4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)
5)......then I don't know what to do
Sum of Quantitymonthsupplier idjanfebGrand Total 754466163150842519337568263901785022875682639410025035075682639500 75682639642523866375682639780012492049756826398209209756826399292292 756826400264264756826401178178757494037292292761034919209209867233456264264(blank) Grand Total245154236931
The code I'm using that results in the following error message: "Run-Time Error '1004':
The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:
Option Explicit
Sub main_prog() Call td_metrics_import Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2") Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4") Call create_graph End Sub...............
I have a pivot table with a field called "created on" which is a series of dates, i want to create a field called "Days in Pipeline" which would essentially be =TODAY()-'Created On'
This doesn't seem to work, i get an error: Your formula includes a function that cannot be used in PivotTable formulas...
I successfully created two PivotTables two days ago, but when I added more rows of data to the source worksheet I could not refresh either PivotTable view. So after much frustration, I deleted both worksheets and again tried to create a new PivotTable using the wizard. I keep getting this error, and have no idea what it is telling me so that I can go about fixing it:
"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."
I have a section of code that takes SO long, but I have to have it. Bascially, I need a pivot field to be set to "All", but there HAS to be a faster way. Here's my
Sub FloorCompareSetter() Dim pt As PivotTable Dim pi As PivotItem Set pt = ActiveSheet.PivotTables("PinPointPivot") ' Speeds up code dramatically pt.ManualUpdate = True 'Set the floor comparison for managers, coaches, and reps ' Make sure all PivotItems along line are visible For Each pi In _ pt.PivotFields("Manager").PivotItems pi.Visible = True Next pi pt.ManualUpdate = False End Sub
Auto Merged Post Until 24 Hrs Passes;Also, note that I have used other techniques to speed up the process:
With Application . ScreenUpdating = False .EnableEvents = False .Calculation = xlManual End With
I am thinking of using a listbox so a user can select a product to graph. What I want to know, in VBA, is how do I use the selection the user makes and use it in a pivot table current page field? The listbox returns a number whereas I want the text. I can use the number when it is linked to a cell and a vlookup to get the text but how do I get the text into the current page field?
I have a column in powerpivot that has Products total amounts. Some of them are negative amounts but I want them to be positive. How would I write an IF statement for it? I want it to say " If Division amount column is a negative, then multiply by -1 but it is a positive , leave it as a positive" .
CPM is a calculated field that uses the formula =cost/(impressions/1000)
The problem is that the numbers in the CPM field are not, in practice, the results of that formula. They are averages (since it's a data field, I had to choose whether it was to be a sum, average, etc.). This means that, for example, the CPM 4.5 on the first row is not a calculation using the two numbers to its left. It is the average of the output of thousands of calculations done on the raw data, in which every line item with a productname of product1 had its CPM calculated, and then all those output CPMs were averaged together.
This is not what I want. I want a field that calculates based on what's already in the pivot table. So that the formula would be more like:
I have created a macro to run different imports on the system and I need a message box calculated at the en displaying the message "Write Total Charges to Import: xxxx".
The total is the Sum of the cells listed on Column B. I have defined my total and need to show the message, and after the user has writen down the total and clicks OK, the cell showing the total is deleted. how to make it work?
The calculation to display is the result of (Range("B" & EndRow + 2) = "=SUM(B2:B" & EndRow & ")") but when I add the formula next to "Write Total Changes to Import" does not work.
I am using Excel 200o to create a PivotTable but am having problems wiht the Totals. Is it possible to create a PivotTable that includes hidden items in the Totals (row)? I tried the Subtotal Hidden Page Items option but this seemed to do nothing to the Total or subtotal.
Ihave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.
I have a pivot table with 2 row filters (dept and name) and then three columns - Year 1, Year 2. I need to add a calculated field inbetween Year 1 and 2 to show the variance in dollars from ( Year1-Year2) as well as an additonal a column that shows the percentage change between the tw o years . I know I can copy this over and add formuals in Excel but I need to retain the features of the pivot tables due to the 2 row filters?
I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query.
From the exported data, I have the following:
Date opened (MM/DD/YYYY HH:MM:SS AM/PM) Date closed (MM/DD/YYYY HH:MM:SS AM/PM) calendar_stc (this exports in seconds, such as 136) (this equals date closed minus date opened for each record) assignment group
From this export, I'm trying to make a pivot table to show the average calendar_stc per assignment group, per month, per year; but in hours, not seconds.
I thought this was an easy process, create pivot to group row data by years then by months. Then add in assignment group to to the Rows as well. From there, I added to the values section of the pivot the calendar_stc. The default, of course, is to sum each row. But I need the average so I switched it to average. Now it is showing the correct average calendar_stc in seconds per group. To make it hours, I figured I could add in a calculated field. To do that I added the formula of =calendar_stc/60/60 for the calculated field. When I hit okay, it's giving me the SUM in hours for each row. Modifying the field settings to average does not change the numbers. So I went back into the formula and modified =average(calendar_stc/60/60). And that is not working either.
Here is an example to show the math with my formula.
Can I create a calculated field based upon the count of data items in fields as opposed to sums. My data is confidential so here is a dummy example:
Can I take the count of Cars per individual salesmen and divide by the total count of vehicles? Ultimately, I will need to code this into VBA once I know if it can be done.
Vehicles l salesman car1 Bob car2 Kelly car3 Bob car4 Bob car5 Tim car6 Tim car7 John
With this result:
Salseman l Percentage of Vehicles Bob 43% John 14% Kelly 14% Tim 29%
I am trying to create a dynamic pivote table that can be update using a value in a cell. I can get everything updating and working correctly except I am unable to remove the one calculated feild from my pivot table. I am able to remove all other fields without problem. Code below: (lines 13 and 14 is the code that i cant get to work)
Private Sub CommandButton1_Click() Dim dt As String Dim dt2 As String On Error Resume Next dt = Sheets("Executive Summery").Range("M1") dt2 = dt & "2"
I am trying to calculate a ratio for Hours per Ticket by month. I have a pivot table that COUNTS tickets and SUMS hours per month. I was hoping to create a calculated field to the jist of COUNT(Tickets) / SUM(Hours), but this does not work because calculated fields aggregate data.... I was hoping to keep it all in a pivot table so that I can still use slicers.
I use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.
This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.
I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:
I'm getting an error message in when I attempt to create a pivot table calculated field formula using the SUMIF function. My questions are (A) Can SUMIF be used in a pivot table calculated field and (B) If Yes, then why am I getting an error message? To expand more on my second question(B). My scenerio is as follows.
I need to compute the Total Inventory Value (Quantity X Cost) of all our inventory that is aged over 60 days. I have a "Total Value" field that gives me the inventory value at a sku level. also have a "Days Since Last Receipt" (DSLR) field that gives the Days since the Inventory was last Received at a sku level. For Example:
SKU A --Total Value=$100 -- DSLR=50 SKU B --Total Value=$75 -- DSLR =70 SKU C --Total Value=$40 -- DSLR =80
Using the above scenerio and example I tried to execute this calculated field formula: =SUMIF(DSLR,">60",Total Value). However I get an formula error message.
I have a Table with 2 columns "Due Date" and "Completed Date". The pivot table from the table gives the count of each column. I want to have a calculated column giving the % complete, ie. "Count of Completed"/"Completed Date"*100. When I attempt to create this formula (using Pivot Table Options > Fields Items and Sets > Calculated field), I get a DivZero error, even though both columns are not zero. How do I create such a calculated column?
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
The other option would be how to set up conditional formatting to always take on the appearance of Column K of the pivot table. Thus, this calculated section would always appear as though a part of the pivot table and would adjust based on any pivot table items being expanded and collapsed.
