• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • 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.
Doing-Total-in-Data-Model.xlsx
0
happy 1001
Asked:
happy 1001
  • 2
1 Solution
 
xtermieCommented:
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].

and
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!
Doing-Total-in-Data-Model_wSolution.xlsx
1
 
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.

Thanks
0
 
happy 1001Author Commented:
Thank you xtermie
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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