Share via

DAX measure

Luca Moffa 1 Reputation point
2021-01-27T12:06:11.483+00:00

Can you please help me in set a measure in Pivot table to correctly calculate total TWR in Pivot? I have 2 shares A and B and total results using PRODUCTX to calculate total TWR are wrong.

This is the formula I made: =CALCULATE(PRODUCTX(VALUES(Table1[Date]),sumx(Table1,[Return])/sumx(Table1,[CurrentValue t-1])+1)-1)

I can send the trial file excel I used if needed.

Many thanks and regards.
61012-screenshot-2021-01-27-125612.png

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-01-28T07:43:47.507+00:00

    The CALCULATE call is misplaced, those #NUM! values hint at using the safer DIVIDE, and daxformatter.com is a great tool: 

    =
PRODUCTX (
    VALUES ( Table1[Date] ),
    CALCULATE (
        DIVIDE ( SUMX ( Table1, [Return] ), SUMX ( Table1, [CurrentValue t-1] ) ) + 1
    )
) - 1
    0 comments
  2. Lukas Yu -MSFT 5,821 Reputation points
    2021-01-28T08:01:23.257+00:00

    Hi,

    As far as I can see, the "wrong" result is caused by the decimal places in the dax calculation .

    For example :
    61328-image.png

    61329-image.png
    This is why the end result is not the same as your previous test.

    I think your DAX should be the right result.

    =CALCULATE(PRODUCTX(VALUES(Table1[Date]),sumx(Table1,[Return])/sumx(Table1,[CurrentValue t-1])+1)-1)

    And the TWR for each row should be rounded to 2 decimal places since it is in percentage format just as you "TWR+1" column. You could use ROUND or FIXED function to do this.

    Regards,
    Lukas

    0 comments