DAX measure

Luca Moffa 1 Reputation point

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.

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,248 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points

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

        VALUES ( Table1[Date] ),
        CALCULATE (
            DIVIDE ( SUMX ( Table1, [Return] ), SUMX ( Table1, [CurrentValue t-1] ) ) + 1
    ) - 1
    0 comments No comments

  2. Lukas Yu -MSFT 5,816 Reputation points


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

    For example :

    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.


    0 comments No comments