Measure or Calculated Column Dax?

millhouse123

Active Member
Joined
Aug 22, 2006
Messages
335
I have a data from a single table that I am pivoting and I need to calculate a weighted average. I have looked all over for documents on this topic but have not found exactly what I am looking for. What I need to do is regardless of the selection in the filter I need to take the value of the book value in the current row and divide it by the total book value of all records that are showing in the pivot table.

example:
Book Value Result
1. 1000 10%
2. 2000 20%
3. 7000 70%

I figure this should be pretty easy but I have yet to be able to figure it out.
Thanks so much for your help.
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Calculated columns are calculated at refresh and can't have access to what is in your pivot table.

The Calculate Column would be as simple as:

Column = Table1[Value] / SUM ( Table1[Value] )

To have the result reflect only the items in the pivot table you would need to write a measure and use the ALLSELECTED function.

Code:
Measure :=ResultMeasure: = 
DIVIDE (
    SUM ( Table1[Value] ),
    CALCULATE (
        SUM ( Table1[Value] ),
        ALLSELECTED ( Table1 )
    )
)
 
Upvote 0
The formula worked as I requested but it turns out its not what I was hoping it would be. I will do my best to describe what I am looking for and hopefully someone can tell me if it's possible.

What I need is the Book Value weighted average Yield. To this I would divided the book value of each security divided by the total book value of all securities in the same sector. I would then multiply that by the yield of each security and sum at the sector level.


Here is an example to try and clarify


Sector - Security - Book Value - Yield

Fixed - Bond1 - 100 -- 5%
Fixed - Bond 2 100 - 8%
Equity - Stock1 100 1.5%
Equity - Stock2 100 2%


Desired Result:

Sector - Weighted Average Yield

Fixed - 6.5%
Equity- 1.75%
Total - 4.125%
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top