locked
Rollup values after many to one merge RRS feed

  • Question

  • Hello,

    I am just learning Powerpivot and my problem seems like it would be easy to solve, but I'm not sure how to do this calculation.

    The following is an example of my data sets. I merge my files(left hand side files) before I bring into Powerpivot by month and CC(Costcenter) and get something like the datset on the right.

    In my Expense table , the EXPENSE value is unique to each MONTH/CC combination. My Revenue table lists REVENUE broken down by MONTH,CC,QUEUE and POOL.  When I merge by MONTH/CC, the EXPENSE value is repeated in each MONTH/CC combination in the REVENUE file. So, when REVENUE is displayed as a value in power pivot it sums the value for every rendition of the MONTH/CC combination.

    Should I merge the files differently?  Is there a DAX function to only display the rolled up EXPENSE value?  What am I doing wrong here?

    Thanks in advance!

     

    REVENUE TABLE         EXPENSE TABLE     COMBINED        
    MONTH CC QUEUE POOL REVENUE   MONTH CC EXPENSE   MONTH CC QUEUE POOL REVENUE EXPENSE
    2011-01 123 100 1 100   2011-01 123 1000   2011-01 123 100 1 100 1000
    2011-01 123 100 2 200   2011-02 123 2000   2011-01 123 100 2 200 1000
    2011-01 123 100 3 300   2011-03 123 3000   2011-01 123 100 3 300 1000
    2011-01 123 100 4 400   2011-04 123 4000   2011-01 123 100 4 400 1000
    2011-01 123 100 5 500   2011-05 123 5000   2011-01 123 100 5 500 1000
    2011-01 123 100 6 600   2011-06 123 6000   2011-01 123 100 6 600 1000
    2011-01 123 200 1 700   2011-07 123 7000   2011-01 123 200 1 700 1000
    2011-01 123 200 2 800   2011-08 123 8000   2011-01 123 200 2 800 1000
    2011-01 123 200 3 900   2011-09 123 9000   2011-01 123 200 3 900 1000
    2011-01 123 200 4 1000   2011-10 123 10000   2011-01 123 200 4 1000 1000
    2011-01 123 200 5 1100   2011-11 123 11000   2011-01 123 200 5 1100 1000
    2011-01 123 200 6 1200   2011-12 123 12000   2011-01 123 200 6 1200 1000
                                   
              DISPLAY as is.      Desired Display          
                MONTH       MONTH          
                2011-01       2011-01          
              CC REVENUE EXPENSE   CC REVENUE EXPENSE        
              123 7800 12000   123 7800 1000        

    Wednesday, August 24, 2011 4:03 AM

Answers

  • @EazyMark,

    I would combine the data sets as shown below. Since expense is not specific to queue and pool leave the expense column blank for revenue rows and leave the queue, pool and revenue columns blank for the expense rows. This should avoid any problems with double counting.

     

    COMBINED      
    MONTH  CC  QUEUE  POOL  REVENUE  EXPENSE
    2011-01  123 100 1 100
    2011-01  123 100 2 200
    2011-01  123 100 3 300
    2011-01  123 100 4 400
    2011-01  123 100 5 500
    2011-01  123 100 6 600
    2011-01  123 200 1 700
    2011-01  123 200 2 800
    2011-01  123 200 3 900
    2011-01  123 200 4 1000
    2011-01  123 200 5 1100
    2011-01  123 200 6 1200
    2011-01  123 1000
    2011-02  123 2000
    2011-03  123 3000
    2011-04  123 4000
    2011-05  123 5000
    2011-06  123 6000
    2011-07  123 7000
    2011-08  123 8000
    2011-09  123 9000
    2011-10  123 10000
    2011-11  123 11000
    2011-12  123 12000
    Thursday, August 25, 2011 5:20 PM

All replies

  • Hi EazyMark,

    You can make use of SUM function in DAX to return the sum of an expression evaluated for each row in a table. For more information about  SUMX Function (DAX)please see: http://technet.microsoft.com/en-us/library/ee634959.aspx

    If I misunerstand you, please feel free to let me know.

    Thanks,
    Challen Fu

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, August 25, 2011 6:06 AM
  • Challen,

    Thanks for responding. I guess my problem is I don't know how to code it.   I've looked for examples, but can't get it to work.

    In my COMBINED table you can see the EXPENSE is repeated for every line.  How would the function look if I just wanted to return the single value instead of summing the entire column?  

    Thanks again,

     


    EZ
    Thursday, August 25, 2011 4:46 PM
  • @EazyMark,

    I would combine the data sets as shown below. Since expense is not specific to queue and pool leave the expense column blank for revenue rows and leave the queue, pool and revenue columns blank for the expense rows. This should avoid any problems with double counting.

     

    COMBINED      
    MONTH  CC  QUEUE  POOL  REVENUE  EXPENSE
    2011-01  123 100 1 100
    2011-01  123 100 2 200
    2011-01  123 100 3 300
    2011-01  123 100 4 400
    2011-01  123 100 5 500
    2011-01  123 100 6 600
    2011-01  123 200 1 700
    2011-01  123 200 2 800
    2011-01  123 200 3 900
    2011-01  123 200 4 1000
    2011-01  123 200 5 1100
    2011-01  123 200 6 1200
    2011-01  123 1000
    2011-02  123 2000
    2011-03  123 3000
    2011-04  123 4000
    2011-05  123 5000
    2011-06  123 6000
    2011-07  123 7000
    2011-08  123 8000
    2011-09  123 9000
    2011-10  123 10000
    2011-11  123 11000
    2011-12  123 12000
    Thursday, August 25, 2011 5:20 PM