• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA pivot with calculated field which should loop through row field

bijicha

Member
Team,

please help I wish to create a VBA pivot for all employees, with sum of wages and calculated field of wages plus bonus, the calculated field bonus percentage should change according to the row category of employees

It should be like below for clerk it should be 5%, coordinator should be 10% etc..

sum of daily wagesWages + Bonus (calculated field)
clerk10001050
Coordinator110001100
Coordinator210001100
Coordinator310001100
Controller10001200
Manager10001300
 
That usually can't be done via calculated field. As Pivot Table's calculated field will SUM() values before any other calculation takes place.
Though it may be possible depending on your source data layout.

I'd recommend uploading sample workbook, that mirror your actual workbook, with manually calculated examples of desired out put. Remember to desensitize the data.
 
Hi Chihiro.. thanks for the feed back, i am attaching the excel file, if possible give me a solution through VBA
 

Attachments

  • calculated field sample.xlsx
    19.8 KB · Views: 6
What version of Excel do you use?

And based on your sample... there really is no point in using pivot as there are no aggregation being performed on the raw data.
 
Hi Chihiro .... Excel 365. i need to populate this pivot on daily and hourly basis as and when the employees gain any additional benefits.. say around 313 employees, it should be in VBA pivot (based on my knowledge) which will all time refreshed based on the update data... do you have any other suggestion other than pivot...
 
bijicha
Your original It should be like below for clerk it should be 5%, coordinator should be 10% etc.. could make this like a challenge
with Your sample file (#3).
Because there are not clear connection with which uses which bonus %.
Create a sample file which is as near as You normally used file with needed details.
... and is Your needed output as in Your sample file or something else?
 
Hi Vletm, sorry please see the attached excel file.. which will give you more clarity. leave the % , i want to show a pivot with calculated field for all employees multiply with 0.93, but only few of them's value should be multiply by 1 is that possible?
 

Attachments

  • calculated field sample.xlsx
    19.8 KB · Views: 3
Does your raw table actually look like A1:B7 range?

Sample should be representative of your actual data structure. As is, there is no point in vba or pivot table.

As 1 line of raw data = 1 line of pivot data.
 
bijicha
Your #7 file looks same as Your previous file which won't give any answers to my questions.
Should I understand that You skipped my #6 reply's all lines?
Without answers which could help YOU, this will be a challenge.
 
Hi Chihiro, i added the new file, yes raw table will be A1:B13, but it will extend to may be 320 lines, pivot must show sum B1:B13 and the calculated field with X0.93.
Few of them should show instead of X0.93, X1.0. A1:A13 must have same data multiple time so need the pivot.

Hi Vletm, please check the attached file, i think i made it more clear..
 

Attachments

  • calculated field sample.xlsx
    16.6 KB · Views: 3
See attached. Using Data Relationship between Table1's Employee code to Table2's Employee code.

Table2 holds list of adjustment per employee.

Added following measure:
64080

See attached.
 

Attachments

  • calculated field sample (2).xlsx
    119.4 KB · Views: 7
Hi Chihiro/Vletm, this is working well, but i am looking for a VBA code, which will incorporate the same concept. Below is the code to do the calculated field value, can i add a loop to check the employee code/codes are few particular, then multiply with 1 else multiple with 0.93

64125
 
bijicha
Replies #9, #11
... Your: It should be like below for clerk it should be 5%, coordinator should be 10% etc..
Why do You use Pivot-table, if You do a * b = c?
 
Hi Vletm, this data/value is deriving form a combined sheet of 11 different sheets. and the combined sheet is creating regularly to catch the updates of every day's on each sheet.. so static formula/page will not serve the purpose.. I was able to create the combined sheet, pivot, calculated value field etc through VBA, but stuck at this point need a calculated value field with exception/loop.
Is there any way to add a loop to check while populating the current calculated field, or rewrite the code in such a way it check each data and populated the calculated field multiply with either 0.93 or 1
 
bijicha
Anyway, I have tried to get answers... but no... hmm?
Your sample picture didn't give nor You uploaded file same image of Your thread!
If You won't give answers ... this is a challenge!
Okay, if Your Pivot-table give needed results WITHOUT those calculated results ( eg *0.93) then there would be other way to get those - okay?
BUT, as in Your #1 reply - You should have a list ... which employees per 'title' like clerk have other than *1 to solve Your needed calculated results.
If You cannot / won't show that kind of list then ... good luck!
 
You can't loop through Calculated field as Pivot Table does not work in that manner.

Since you have Office 365, you should append (union) data from different sheets using it, and then load to the model and use the relationship and the DAX measure I used in previous post. But again, we are not mind reader, and you must provide us with complete representation of your workbook in order for us to give you help.
 
Hi vletm/[B] Chihiro[/B], I think, the attached file will help you to find a solution Please check the attached with Macro Ctrl+Shift+V

Need to get few calculated field value without calculation.
 

Attachments

  • Location Daily Wages tracker 2019.xlsb
    140.7 KB · Views: 2
If you can't use PowerQuery or Data model, then easiest solution is to add helper column in the combined table.

Just modify your code to add something like below after your For J = 2 to 5 loop...
Code:
With Sheets("Combined")
    .Range("E1") = "Calc"
    .Range("E2:E" & .Cells(Rows.Count, "B").End(xlUp).Row).Formula = "=IF(AND(B2<>8000000105,B2<>8000000035),D2*0.93,D2)"
End With

And remove your calculated field, and add following...
Code:
    ActiveSheet.PivotTables("CH-3 Values").AddDataField ActiveSheet.PivotTables("CH-3 Values").PivotFields("Calc"), "Value*0.93", xlSum

Resulting pivot should look like...
64181
Note: I didn't add formatting. Choose the one that suites your need.
 
Back
Top