calculated field

  1. M

    Pivot Table - Values as a % of Maximum of Parent Grouping

    I know that title sounds a bit confusing. I have various fees grouped by departments. Customers may only belong to one department, but they may pay one or more fees in their department. I have revenue by fee type, as well as the count of customers that paid each fee type. I would like to...
  2. M

    Pivot Table: Calculating Average % Across Years

    Hello, I have a Pivot Table with Years as the top-level column, then two fields underneath, Cash and Operations. The values in each field are currently calculated as % of Parent Column Total, so Cash and Operations are shown as percentages of each year's total. I would like to also create a...
  3. V

    How to use a Pivot Table to sum the last 12 columns

    Hi Everyone, hope you can help....as usual. I have a pivot table based on an enormous database which essentially summarises each month points by individual. I need to keep all historical columns in the pivot (this is going back to 2012) but would like the pivot to calculate automatically the...
  4. T

    Pivot Table Calculated Field subtotals

    I am working on a pivot table based on data that has a region, a sub-region, dates, and then the available work potential in each sub-region by date. I need to do two things, 1. I need to create a calculated field based on the available work potential 2. I need to change the subtotals to...
  5. M

    Pivot Table Calculated Field Error

    I have a pivot table and one of the values I changed the value field setting to "AVERAGE" but the calculated field is calculating based off of sum. How can I get the calculated field to calculate off of the average? The Surplus Shortage field is my calculated field where I am taking Inventory...
  6. M

    Question calculated field pivot table

    Hello, I have a question. I would like to calculated ratio's from a database with balancesheets in a pivot-table. The data is as follows: <tbody> Company Balance Sheet no Description Value Year Apple 3 Cash 25 2018 Apple 4 Inventories 35 2018 Apple 5 Short Term debt 75 2018 Facebook 3...
  7. S

    Calculated Field in Pivot Tables

    Hello, I have the below fields in my spreadsheet that I would like to place in a pivot table. I want to calculate the sales turn for the aggregated with the formula (BOH $/Sales $)*# of weeks in the month. I have tried using an if statement in the calculated field =if(weeks in month>4, (BOH...
  8. J

    Pivot Table Difference From (or maybe something else)

    I'm trying to calculate the difference in TY vs LY numbers in a pivot table. My set up is: Rows: Client Name Sale Date (grouping by month) Columns: Years (for the TY vs LY comparison) Sum Values: Sum of Sales For example <tbody> 2018 2019 Client 1 May 100 1000 </tbody> I want to...
  9. H

    Calculated Fields Greyed out in Pivot Table with Data Model

    I watched the YouTube Video - Budget vs. Actual - Podcast 2016 where a data model is used to compare an actuals table to a pivot table, by using intermediary "Joiner tables." It's a great solution! I am trying to expand the idea by introducing a calculated column for the difference between...
  10. S

    Pivot table and divide one calculated column by another column

    <style></style>I have one column that counts all the document names on a certain date and sums the values on that day. I am wanting to add a column that will divide the Duration by the sum of the Doc Names on that day. I cannot get the calculated field to do the correct calculation. I am...
  11. J

    Using % of Parent Row Value in a Calculated Field

    I am looking to create a calculated field in my pivot table using the value generated from the 'percent of parent row'. I'd like to multiply it by another column but am finding that this might not be possible. Is there a way to do this? Or if not, is there an alternative? The pivot table has 4...
  12. P

    custom error bars in pivot chart based on calculated field

    Is it possible to create custom error bars in a pivot chart that are based on a calculated field? I would like to create a bar chart representing PROPORTIONS with error bars that represent the CONFIDENCE INTERVALS. My data contains N items that either can be "failed" (0) or "passed" (1)...
  13. D

    Need to incorporate popultation report into pivot table to calculate error rate

    I have an Excel 2010 workbook that includes the detail for a portion of the population of data. The portion was selected based on certain criteria. Research was completed to test compliance. The results are displayed in a pivot table. I need to calculate error rate based on a second report that...
  14. A

    Incorrect Count Subtotal in Pivot Table Calculated Field

    Hello, I have a report with a date slicer that returns, by sales manager, by sales rep, by account, the current period revenue and prior year revenue for the sliced date range. I need to add a column that counts the number of accounts with revenue in the current period. The data source is such...
  15. C

    PivotTable - calculation on Average data

    Hi Any help would be greatly appreciated as I'm a novice at pivot tables. I have created a pivot table to summarise a large data set . I have calculated the average size within the pivot table and a column that shows the max size allowed within spec. What I would like to do is to have a...
  16. A

    Create calculated field for pivot table, subtracting 2 following Qtrs

    hi all, This is the SQL data I imported into power pivot: <tbody> ProfitCenter_Name Account_Group Project_Group Fiscal_Year Q_FY Edition Edition_Type Revenue A AA AAA 2018 Q4/18 Actual Internal 5 A BB BBB 2019 Q1/19 Actual Internal 9 B CC CCC 2018 Q3/19 Other External 10 A AA AAA...
  17. M

    Pivot with both count distinct AND calculated fields

    Hey guys first post, good morning (well here anyway) to you all. I have looked for a solution to this and am hitting a bit of a blank, I am sure it must be answered somewhere Excel 2016. I want to add distinct count to a pivot table. in its simplest form, its sales rep, order counts, and...
  18. G

    Can a Pivot Table Calculated Field Return Text Values?

    I'm trying to use a Pivot Table Calculated field formula of IF(Column 50>20,"YES","NO"). It is only returning #VALUE . The formula works when I change the return values to numbers. Is it not possible to return text from a pivot table calculated field?
  19. J

    Finding Fraction of 2 calculated fields in Pivot Table

    I have a table which summaries a list of purchases made by customers. In this table it has 3 columns, the customer ID, their max budget and the purchase amount. I wish to make a pivot table which shows compiles the sum of purchases for each customer and the % of their budget used. I had no...
  20. T

    Pivot Table & Calculated Fields?

    Hi everyone, Apologies if my question has been posted or solved before, however I could not find so here I go. I have a series of HR headcount data, which contains various columns of 1's and 0's. I am trying to figure out turnover or attrition, but I would like to calculate this within a...

Some videos you may like

This Week's Hot Topics

Top