Hi,
I have a 'Is it possible?' question.
I have a table like below:
Daily Returns - Table 1
| Date | AAA | AA | A | BBB | BB | B | HY |
| 2019-07-01 | 0.02% | -0.02% | 0.04% | 0.03% | 0.03% | 0.03% | 0.14% |
| 2019-07-02 | 0.01% | 0.00% | -0.02% | 0.05% | 0.01% | 2.07% | 0.08% |
| 2019-07-03 | 0.01% | 0.01% | 0.01% | 0.02% | 0.02% | 0.03% | 0.17% |
| 2019-07-04 | 0.01% | 0.01% | 0.01% | 0.02% | 0.02% | 0.03% | 0.01% |
There is also a table like below:
Betas - Table 2
| Betas | AAA | AA | A | BBB | BB | B |
| | 0.16 | 0.31 | 0.42 | 0.67 | 1.08 | 1.05 |
| | 0.20 | 0.5 | 1.2 | 0.20 | 0.5 | 1.2 |
| | 0.30 | 0.9 | 2 | 0.30 | 0.9 | 2 |
| | 0.40 | 0.4 | 3.4 | 0.40 | 0.4 | 3.4 |
Now, I want to provide the user with an option to select a beta for each field using a slicer option on the dashboard for each field.
Next, I want to have a calculated table as below:
where each cell is calculated using the following example:
Red highlighted cell = AAA Daily Return - AAA Beta (value selected by the user in the slicer) * HY Daily Return (Daily Returns for the corresponding dates)
Beta Adjusted Daily Returns - Table 3
| Date | AAA | AA | A | BBB | BB | B |
| 2019-07-01 | 0.00% | -0.06% | -0.02% | -0.07% | -0.13% | -0.12% |
| 2019-07-02 | 0.00% | -0.02% | -0.05% | 0.00% | -0.07% | 1.98% |
| 2019-07-03 | -0.02% | -0.04% | -0.06% | -0.10% | -0.16% | -0.15% |
| 2019-07-04 | 0.01% | 0.01% | 0.01% | 0.01% | 0.01% | 0.02% |
And I will have another calculated table:
where each cell is calculated as sum of beta adjusted daily returns till that particular date
Beta Adjusted Cumulative Returns - Table 4
| Date | AAA | AA | A | BBB | BB | B |
| 7/1/2019 | 0.00% | -0.06% | -0.02% | -0.07% | -0.13% | -0.12% |
| 7/2/2019 | 0.00% | -0.08% | -0.07% | -0.07% | -0.20% | 1.86% |
| 7/3/2019 | -0.02% | -0.12% | -0.13% | -0.17% | -0.36% | 1.71% |
| 7/4/2019 | -0.01% | -0.11% | -0.12% | -0.16% | -0.35% | 1.72% |
Now, making all of this is simple enough. But the catch is, I need the Beta table to be an user input in slicer form such that the user selects the values of the betas for different parameters which then calculates Tables 3 and 4 and displays them in a visual.
To summarize:
1. Table 1 is raw data available from the source.
2. Table 2 needs to be an user selection from a dashboard slicer.
3. Tables 3 and 4 will be calculated using Table 1 and user-selected values in Table 2.
How can I make this possible in a PowerBI report? Need to understand how Table 3 will be created/coded in particular.
Will be really grateful if someone could help me figure this out! Thanks a ton.
