• 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.

Unable to create a calculated field in a Pivot table using combined data sources.

A

avanv

New Member
This worksheet contains: 1) two data tables that are linked to two MS Access database tables, and 2) a pivot table that uses both of them as its source data.

In the pivot table ("combined Pivot"), I am unable to create a calculated field of any kind. I need one to the right of 'Time Worked' that shows the difference (Variance) between the MOU and the Time Worked (MOU - [Time Worked]).

For the grand total of at the bottom, I was able to add a calculated field outside the pivot table, which of course breaks if I filter on Client. I really need the calculated fields to be inside the pivot table.
 

Attachments

  • ClientMOU_TimeWorkedDetail.xlsx
    648.6 KB · Views: 4
What version of Excel are you using?

The reason Calculated Field isn't available... is due to the fact Pivot Table is based on Data Model (i.e. using Relationship).

Depending on the version of Excel, you should be able to use DAX measure to accomplish what you need.

In some older versions, DAX is either very limited or unavailable. In that case, you may have to create single flat table rather than two related tables (i.e. Don't use data model and use traditional pivot using table/range as source).
 
The reason I am using two table is because if I don't it creates other problems, namely, the MOU field does not total correctly(it sums the field rather than show each unique instance of it).
What is DAX and how do I access it. (I'm using the most recent version of Excel.)
 
Hmm, you may not have full DAX, should have access to basic functions.

I'm out for the night, but will check back tomorrow.
 
Oi, your model had a lot of junks. And some error.

At any rate, see attached.

See fx (DAX measures) added to each table in your model.

Caption/Custom Name for value fields were adjusted to match your current set up.

You can right click on each measure to see set up.
 

Attachments

  • ClientMOU_TimeWorkedDetail.xlsx
    671.4 KB · Views: 1
You must log in or register to reply here.
Back
Top