Use a function in a custom field in pivot table

Question

I'm trying to add a custom field to a pivot table using sumif function.

My data has records for both hours and OT. I would like a pivot table to show the total hours for all types i.e. (Reg, OT, Sick time, etc) . The custom field I need to add would sum all OT hours and divide by the total hours. I'm looking for the best way to do this

sergeibaklan · Answer

Hi Jack,
&nbsp;
If your table is added to data model you may right click on table name like here

and add measure
=CALCULATE(SUM(Table1[Hours]),Table1[Type]="OT")/CALCULATE(SUM(Table1[Hours]),ALL(Table1[Type]))
or divide on SUM to show % of OT to all other types.
Please see attached.

Forum Discussion

Use a function in a custom field in pivot table

1 Reply