# Rollup values after many to one merge

• ### 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?

 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

• @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