I am trying to create a calculated field in a pivot table that essentially takes the Total Days divided by the # of days that was in a given month/Year.
Below are the data columns I am using. The first 5 columns get pulled in a query data dump that I don't control. The last 2 columns are driven by formulas I created to help determine how many days were in a given month in a given year (I did it this way so that I could account for leap years).
The "Date" column is a concatenate formula =IF(N2="","",(CONCATENATE(N2,"/",1,"/",M2))) - this gives me a short date based by combining my "mnth" and "yr" column.
The "# of Days" column has the formula =IF(X2="","",(DAY(EOMONTH(X2,0)))) - this gives me the # of days there were in that month and year.
Now comes my problem.
In the pivot table I built off this data that gives a 5 year history by month, I need to do a calculated field that takes the "Total_days" of a given month and year divided by the # of days that was in that month and year.
I thought by adding that "# of Days" column, I would use the Min, Max, or Average formula in my calculated field I would create for the pivot table. But it doesn't work.
For example, if the month/year is Feb 2022 and the sum "Total_days" column of all Feb 2022 rows adds up to 3,000, i want to divide that 3,000 by 28 because that is the number of days there was in Feb 2022.
But the calculated filed, whether I use =Min(# of Day), or =Max(# of Day), or =Average(# of Day), keeps giving me a sum total of the "# of Days" column (see 2nd pic below).
What I would like to see or at least take into consideration in my calculated field formula is to return something that looks like the 3rd pic below. I was able to create that my just dropping the "# of Days" column in a pivot table and selecting "min" under the Value Field Settings.
I have to be missing something. Surely I should be able to create a calculated field for my pivot table that takes my "Total_days" column divided by the Min of "# of Days" column.
Below are the data columns I am using. The first 5 columns get pulled in a query data dump that I don't control. The last 2 columns are driven by formulas I created to help determine how many days were in a given month in a given year (I did it this way so that I could account for leap years).
The "Date" column is a concatenate formula =IF(N2="","",(CONCATENATE(N2,"/",1,"/",M2))) - this gives me a short date based by combining my "mnth" and "yr" column.
The "# of Days" column has the formula =IF(X2="","",(DAY(EOMONTH(X2,0)))) - this gives me the # of days there were in that month and year.
Now comes my problem.
In the pivot table I built off this data that gives a 5 year history by month, I need to do a calculated field that takes the "Total_days" of a given month and year divided by the # of days that was in that month and year.
I thought by adding that "# of Days" column, I would use the Min, Max, or Average formula in my calculated field I would create for the pivot table. But it doesn't work.
For example, if the month/year is Feb 2022 and the sum "Total_days" column of all Feb 2022 rows adds up to 3,000, i want to divide that 3,000 by 28 because that is the number of days there was in Feb 2022.
But the calculated filed, whether I use =Min(# of Day), or =Max(# of Day), or =Average(# of Day), keeps giving me a sum total of the "# of Days" column (see 2nd pic below).
What I would like to see or at least take into consideration in my calculated field formula is to return something that looks like the 3rd pic below. I was able to create that my just dropping the "# of Days" column in a pivot table and selecting "min" under the Value Field Settings.
I have to be missing something. Surely I should be able to create a calculated field for my pivot table that takes my "Total_days" column divided by the Min of "# of Days" column.
