Measure or Calculated Column Dax?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Measure or Calculated Column Dax?

  1. #1
    Board Regular
    Join Date
    Aug 2006
    Posts
    331
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Measure or Calculated Column Dax?


    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.
    Last edited by millhouse123; Dec 1st, 2017 at 08:42 PM.

  2. #2
    Board Regular
    Join Date
    Apr 2015
    Posts
    283
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Aug 2006
    Posts
    331
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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%
    Last edited by millhouse123; Dec 2nd, 2017 at 03:56 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2015
    Posts
    283
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Measure or Calculated Column Dax?

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com