+ Reply to Thread
Results 1 to 9 of 9

Calculcated field Power pivot: Sum only two items from a certain field.

  1. #1
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    Calculcated field Power pivot: Sum only two items from a certain field.

    Hello friends, Hope all is well!

    Please take a look at the images.

    Please help me create a calculated field from a power pivot. I wish to combine / SUM the results:

    i.e. Adding the results of Short-term borrowings + Long-term loan

    these two items are part of the field “Name”

    Thank you so much in advance!
    Attached Images Attached Images

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    Hello,

    sorry, it is not clear what you want to achieve. Adding the two will be the same as summing the whole column.

    Can you explain in a bit more detail? For your data set in the screenshot, what would be the expected result? Given that the values are all 0, the whole question does not make sense.

  3. #3
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    Thank you for your reply, and sorry if my question was not clear.

    I added a new image, that has data. As you know a field has many items in it.
    I want to combine the loans under one name, so I want a calculate field that would sum the Short Term Borrowing +
    The Long-Term Loan.

    Can a calculated field sum/add certain items of a field together? How? DAX?

    Thank you very much!
    Attached Images Attached Images

  4. #4
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    Hi! I finally figured it out:
    =CALCULATE(SUM([VALUE]),FNDB[Name]="Long-Term Loan")+=CALCULATE(SUM([VALUE]),FNDB[Name]="Short Term Borrowing")

    May I ask for one follow-up question, how can I choose two in one formula? i.e.
    =CALCULATE(SUM([VALUE]),FNDB[Name]="Long-Term Loan","Short Term Borrowing") is Not working.

  5. #5
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    I still don't see the point of that. When you build a measure or calculated field to sum the whole column, then you create a pivot table and drag the "name" column into the rows, it will split between "short term" and "long term".

    What is the reason that you want this as a calculated field?

    If you want to combine the two conditions into one formula you can use

    =CALCULATE(SUM([VALUE]),FNDB[Name]="Long-Term Loan" || FNDB[Name]="Short Term Borrowing")
    Last edited by teylyn; 01-14-2019 at 03:24 PM.

  6. #6
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    Hi, thanks for the reply.

    The Excel sheet picks up common rows/items of a field and combine the common items. e.g. Loans together, Liabilities together, Assets together etc.

    Can you please help me fix the DAX formula: =CALCULATE(SUM([VALUE]),FNDB[Name]="Long-Term Loan","Short Term Borrowing")

  7. #7
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    I already did in my previous post. I edited it after posting, so you may have missed it.

  8. #8
    Forum Contributor
    Join Date
    03-12-2014
    Location
    Riyadh
    MS-Off Ver
    Office 365
    Posts
    129

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    thanks again, applied your formula, and have this error.

    so sorry for bothering
    Attached Images Attached Images

  9. #9
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Calculcated field Power pivot: Sum only two items from a certain field.

    I cannot reproduce that. I can add the calculated field without any problems.
    2019-01-15_10-21-58.png

    check that your formula references the correct column names.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] We can't summarize this field with Sum (power pivot)
    By sovietchild in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-23-2020, 07:14 AM
  2. [SOLVED] Sort pivot table by field 2 items (not field 1)
    By csmith-han in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 06-21-2017, 07:58 PM
  3. Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  4. Error in power pivot calculated field
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2016, 01:42 PM
  5. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  6. Replies: 3
    Last Post: 03-03-2012, 12:16 PM

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