• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Excel - doing Sum inside Data Model

Hi Experts

I am doing some experiments in Excel Power Pivot Data Model and I am a total newbie in it. So I am not being able to figure out a method by which I can ADD Multiple Rows to get the Total or Sum Values for them.

I want to add the values for each row which have same value in xdate column, symbol column and putcall column.

I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data.
happy 1001
happy 1001
  • 2
1 Solution
In the spreadsheet you state that you want
1) I want to add a NEW COLUMN named [oi_value], which is calculated by multiplying 2 columns named [price] and [oi].

2) do AGGREGATION based on this newly calculated column of [ oi_value ] in such a manner, that instead of getting the values for each strike separately, I get just one value for each symbol call and put.

For (1) you simply enter the calculation to the next free column, =[@price]*[@oi], and name the column oi_value

For (2) you can use SumIfs, Note that you will get the sum for each row (which may be replicated) for each combination of the same values in xdate, symbol and putcall columns

Check the attached file.

Hope this helps!
happy 1001Author Commented:
@xtermie, thank you so much for trying.

But I need to do this inside the Power Pivot Data Model and not in the excel sheet table.

And secondly, there should be just One Single Row for each Date per symbol for putcall column having values of C and P.

happy 1001Author Commented:
Thank you xtermie
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now