Power Pivot - What formula to use?

[arminbhz]

Hello,
I receive a huge monthly report from my internal system that shows the book value and book yield for the entire portfolio by each position. I want to have a pivot table that shows me my weighted average book yield by each sector and the entire portfolio. The way I normally accomplished this was by adding an extra column (Book Value*Book Yield) to my original data and then doing a calculated field with this formula: 'Book Value*Book Yield' / 'Book Value' which it gave me the weighted average book yield by sector and the entire portfolio. (I've attached the sample file)
I know there is a way that I can accomplish this by PowerPivot without having to manually add the extra column and doing a calculated field each time but I can't figure out the formula in DAX that gives me the same result. I basically want to Paste my report and refresh the Pivot Tale to get the results. Any ideas?

[63falcondude]

Convert your data into a table > Power Pivot > Add to Data Model (I usually bring it into Power Query first since I might have to make transformations in the future, and then close & load to connection only and add to data model)

Once it is in the data model, you can use this DAX measure:

Weighted Average Book Yield:=sumx(Table1,Table1[Book Value]*Table1[Book Yield])/sum(Table1[Book Value])

See attached

[arminbhz]

That's exactly what I was looking for. Thank you so much!