+ Reply to Thread
Results 1 to 5 of 5

PowerPivot - SUMPRODUCT(SUMX) within same column

  1. 04-22-2016, 08:45 AM #1
    snakeCZ
    snakeCZ is offline
    Registered User
    Join Date
    08-26-2014
    Location
    Prague
    MS-Off Ver
    2016
    Posts
    8

    PowerPivot - SUMPRODUCT(SUMX) within same column

    Hi,

    I can't seem to find out how perform sumx function for values that are in the same column (as in the example below). I am trying to get result 2014: 65=(2*10)+(3*15) and 2015: 160=(3*20)+(4*25).

    When I get Price and Quantity as a separated measure (Price:=CALCULATE(SUM([Value]);Data[Item]="Price")) I get sum of all values in a year, which is not what I want, but I can't do it without sum. Do you have any idea how to do it?

    Example:

    Item Year Month Value
    Price - 2014 - 1 - 2
    Price - 2014 - 2 - 3
    Price - 2015 - 1 - 4
    Price - 2015 - 2 - 5
    Quantity - 2014 - 1 - 10
    Quantity - 2014 - 2 - 15
    Quantity - 2015 - 1 - 20
    Quantity - 2015 - 2 - 25


    Thanks a lot
    Last edited by snakeCZ; 04-25-2016 at 03:43 AM.
    Reply With QuoteRegister To Reply
  2. 04-23-2016, 12:20 PM #2
    vikit1991
    vikit1991 is offline
    Registered User
    Join Date
    04-16-2016
    Location
    mumbai
    MS-Off Ver
    2013
    Posts
    1

    Re: PowerPivot - SUMPRODUCT(SUMX) within same column

    There is no function like SUMX.
    <May be that is designed in your excel for perticular reason.
    Please check name manager if they have defined SUMX for range. Shortcut- Alt+m+n to go name manager
    Reply With QuoteRegister To Reply
  3. 04-23-2016, 12:39 PM #3
    southward
    southward is offline
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: PowerPivot - SUMPRODUCT(SUMX) within same column

    Suggest you upload a sample file. It is easier to determine a cause and effect with a file.
    Reply With QuoteRegister To Reply
  4. 04-23-2016, 01:21 PM #4
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,144

    Re: PowerPivot - SUMPRODUCT(SUMX) within same column

    Why is your data organised this way rather in row per record i.e. with price and quantity?
    Reply With QuoteRegister To Reply
  5. 04-25-2016, 03:43 AM #5
    snakeCZ
    snakeCZ is offline
    Registered User
    Join Date
    08-26-2014
    Location
    Prague
    MS-Off Ver
    2016
    Posts
    8

    Re: PowerPivot - SUMPRODUCT(SUMX) within same column

    This problem is particularly for PowerPivot and since there is no "sumproduct" function, "SUMX" works as a substitute.

    There are tens of items within the data model, therefore it is easier to have it in that way. The example is just simplified version.

    Unfortunately, I can't upload the real model since it is work related in very sensitive.

    In normal Excel, I believe, there would be easy way to do it but PowerPivot makes it even more complicated.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel pivot table tied to POWERPIVOT won't refresh without opening POWERPIVOT?
    By Rhino_dance in forum Excel General
    Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  2. [SOLVED] Powerpivot formula to show value that appears most in a column
    By AlexandraT in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 03-13-2015, 11:23 AM
  3. SUMX-Powerpivot Will not work with my data
    By Sweetsticks in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-28-2014, 12:59 PM
  4. Convert sumproduct formula for use in powerpivot?
    By rskadk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2014, 04:04 AM
  5. PowerPivot - Sort by column working in Powerpivot, but not actual pivot table
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  6. PowerPivot Question: How to use powerpivot to analyse multi-column datasets
    By sida in forum Excel General
    Replies: 0
    Last Post: 11-20-2012, 05:34 AM
  7. PowerPivot: Sorting based on a Values column
    By teh bunneh in forum Excel General
    Replies: 0
    Last Post: 06-14-2011, 10:58 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1