Measure or Calculated Column Dax?

Thanks:  0
Likes:  0

Thread: Measure or Calculated Column Dax?

1. Measure or Calculated Column Dax?

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.

2. Re: Measure or Calculated Column Dax?

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 )
)
)```

3. Re: Measure or Calculated Column Dax?

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%

4. Re: Measure or Calculated Column Dax?

=SUMX ( Table,
Table[Book Value] * Table[Yield]
) / SUM ( Table[Book Value] )

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•