Try this in the Data Load Editor:
FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet1);
"NAV Date" as "Trade Date",
FROM [lib://Desktop/Sheet1.xlsx] (ooxml, embedded labels, table is Sheet2);
The link between your 2 tables should then be Trade Date and Account.
Then, in your expression / measure on your chart, to create your calculation:
Sum([Market Value] / [NAV])
Set Number Formatting on the expression to Number, Simple and format 12.34%.
That should be it!
thanks for your answer.
It actually helps me getting the answer I need, but I'm still curious on how to solve the initial problem I wrote down. I figured that after applying the cross table, the data looks like the tables below (I left out a part of the data).
Do you think it would be possible to do a double join or a join on multiple columns, i.e., doing a double join where NAV from NAVTable is included into TradeTable when TradeTable.Account = NAVTable. Account and TradeTable.TradeDate = NAVTable.NAVDate?
Account Trade Date Market Value Ticker Clipper 6 Jan 2015 40 ABC Clipper 6 Jan 2015 50 DEF
Account NAV Date NAV Clipper 5 Jan 2015 1000 Blender 5 Jan 2015 2020 Lindt 5 Jan 2015 1500 Clipper 6 Jan 2015 1010 Blender 6 Jan 2015 2025 Lindt 6 Jan 2015 1500
Si gustas escribir en espaniol pregunta porque quieres usar un cross table ? otra cosa en que casos o cual seran las condiciones para usar los valores de cada columna de la tabla NAVTable?
te paso mi skype y mi email por su gustas te ayudo sirve que practico jejeej
I found the solution.
The solution is changing NAV Date to Trade Date (as you already suggested).
Then a synthetic key is created as Account and Trade Date are in both tables, however, using a left join and dropping a table was enough to include the NAV into the TradeTable for the right Account and right Trade date (and to get rid of the synthetic key.
Left join (NAVTable)
drop table TradeTable;
Now all I have to do is find out how to divide the two, but I guess that will not be the biggest problem.
Thanks for thinking along!