DAX measure

Question

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

Answer 1

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

Answer 2

Hi,

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.

Regards,
Lukas