# Multiple data provider calculations

• ### Question

• I have two SSAS cubes, one which contains dollar values for utility usage, and the other for the actual usage measures (kilowatts, tons, gallons, etc). I have brought them both in to PowerPivot and I would like to create a calculation to come up with a Dollar per Usage metric to compare similar properties.

I build the calculation, but it is returning the same value for every row of data, what am I missing?

The following calc is on the "Usage" tab:

`=SUM([MeasuresELECTRIC])/SUM(USD[MeasuresELECTRIC USD])`

Monday, October 8, 2012 7:15 PM

• Just query a relational source for a date dimension.  Or create one on the fly against a relational source with a script like the one Olaf Helper posted in the Script Center here.  Or create a linked table with the dates you need.

Just make sure your OLAP queries are returning a date you can create the relationship on.  Then you don't have to use a cube to get your date dimension.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Wednesday, October 10, 2012 6:11 PM

### All replies

• Would like to help, but need a bit more detail on your model.  Can you post a diagram or at least details on how the tables are related in your model?  Thanks.

Brent Greenwood, MS, MCITP, CBIP
http://brentgreenwood.blogspot.com

Tuesday, October 9, 2012 4:47 AM
• It's two SSAS Cubes. I created a common key between the two based on the month/year and property id (concatenated the two fields and joined the two tabs)
Tuesday, October 9, 2012 12:17 PM
• I think you need a common dimension table that joins with both in order for the relationships to work. I would create a date table and import that as well and then join the two fact tables to that one dimension table. I think that should work.
Tuesday, October 9, 2012 11:31 PM
• I'm unable to do that because it is an OLAP source... you can't query a cube without having something defined as a measure.

Any other thoughts?

Wednesday, October 10, 2012 1:03 PM
• Just query a relational source for a date dimension.  Or create one on the fly against a relational source with a script like the one Olaf Helper posted in the Script Center here.  Or create a linked table with the dates you need.

Just make sure your OLAP queries are returning a date you can create the relationship on.  Then you don't have to use a cube to get your date dimension.

Brent Greenwood, MS, MCITP, CBIP