+ Reply to Thread
Results 1 to 10 of 10

Power pivot or pivot table for connecting data and creating calculated fields

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Power pivot or pivot table for connecting data and creating calculated fields

    Hi,

    i am not good with pivot or PQ queries and have question about issue which i am struggling right now.

    Screenshot_13.png

    Screenshot_14.png

    And these tables could be joined with fields VolumeKey and KeyVolume somehow.

    I build pivot table and summing up all KeyVolumes disk costs there.
    It would be ok but i have 2 conditions to do:

    If Tier group = "ZX" multiply cost by "Additional Servers" number
    If MountPointName = "/usr/sap", then multiply by 2

    if i would join these tables somehow or build pivot table with calcualted fields maybe it could work?

    As you can see there is Total cost for GeneralID and i have problem how to calculate it.
    This is not straighforward but with conditions.

    Please help with my example attached,
    Goal is to combine these two tables, create calculated fields based on provided conditions and calculate costs per each Tier Group and Total.

    Best,
    Jacek
    Attached Files Attached Files
    Last edited by jaryszek; 01-29-2019 at 01:34 PM.

  2. #2
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    Anyone please,

    Best,
    Jacek

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    Ok Guys,

    i managed to upload 2 tables and merge them by VolumeKey.
    In attachment you will find data.

    Now i have to add conditional column with ifs:

    If Tier group = ZX multiply cost by "Additional Servers" number
    If MountPointName = "/usr/sap", then multiply by 2
    Please help with that.
    And after creating connection can i create pivot table from it somehow?

    Best,
    Jacek
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    anybody?

    Best,
    Jacek

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,155

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    If I understand correctly then I think you will need to make your calculation in column M of the Merge 1 table.
    One way to do this would be using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You could then use 'CalculatedCosts' in the pivot table as modeled on Sheet2.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,121

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    Just add a custom column to your query using a formula like:

    =if [Tier Group] = "ZX" then [Additional Servers] * [DiskCost general] else if [Table3.MountPointName] = "usr/sap" then [DiskCost general] * 2 else [DiskCost general]

    PS I agree with Mynda - you need to do a course.
    Rory
    Sue, you're shouting at tea

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    hi rorya and JeteMc,

    thank you
    Hehe the world is so small!

    I handled all issues connected with PQ
    And my company will not spend any money for me so the forum is my only hope ;-)

    Here problem is solved,
    Best,
    Jacek

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,121

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    Quote Originally Posted by jaryszek View Post
    And my company will not spend any money for me
    I know the feeling.

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,023

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    this is why i love this forum and you Guys ;-)

    Best wishes for you!
    Jacek

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,155

    Re: Power pivot or pivot table for connecting data and creating calculated fields

    Below is a link to a comprehensive Excel 2016 course from Highline college. The videos and downloads of Excel files that go along with the class are free. I also notice that beneath the videos is a discussion where the instructor replies to comments/answers questions as recently as two months ago.
    https://people.highline.edu/mgirvin/...8Excel2016.htm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1