I have a query that pulls data where when I do a Pivot Table I can separate the field (Transaction Type) and group it into Estimate and Actual (AP Cost). I was trying to figure out if there was a way to subtract the groups, Estimate & AP Cost, but I can't see a way of doing it. Any ideas? I have an example of the data plus a pic of the pivot table in the attachment.
Thanks,
Perhaps the information in the linked video will be helpful
https://www.bing.com/videos/search?q...B&&FORM=VDRVRV
For future reference, it may prove more helpful to include an actual pivot table (as opposed to a picture) that relates to the data.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
JeteMc,
Thank you for the video it did help, but now it's throwing up an error that there are too many records to complete this operation and so I don't know what to do. As far as what I uploaded, it's only because in the past that I have tried to upload items here and there a very low threshold for the data that you can upload for a file size.
Tony
I can't say that I have ever seen "there are too many records to complete this operation" as an error for a pivot table.
Just off the top of my head I am thinking that the data must be in the hundreds of thousands of rows and perhaps if it was placed in a data model you could then use a explicit DAX formula and display the result in Power Pivot.
Below is a link to a very long (2 hrs+) video produced by an instructor at Highline College that explains Power Pivot and DAX formulas in detail. If you select the SHOW MORE link below the video there are ways to jump to certain topics.
https://www.youtube.com/watch?v=Rbkbr89cuHo
To be of more help I feel that we would need to see some data, preferably enough so that you get the error message, along with a pivot table. By the way if you "Zip" the file then the site will allow you to upload up to 9.77 Mb
Let us know if you have any questions.
The data is only in about 16,000 rows. If I delete the solve order (which is the formula that I create for it to subtract the difference) the pivot table doesn't have a problem, but the field that I want is removed. I have done bigger pivot tables with calculated fields, etc, but this is the first time trying to do a calculated item where I am subtracting from essentially the same field, Cost-AP cost. So I am guessing I have an issue somewhere?? I have always used the ms query to import with, didn't know anything about Power Query or the Power BI so I think I have some new subjects to research and play around with. I will try to get what I have uploaded once I get a version of zip on my computer as the company I work for is cheap and the version that I had was a test instance that was only good for 10 days, so back to IT to find a solution.
Thanks,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks