Forum Discussion
Risk Adjusted Calculations
HI Anonymous,
You can refer to the following steps if they are suitable for your requirements.
Steps:
1. do 'unpivot columns' on 'system nr' fields to convert them to attribute and value.
2. remove 'system nr' from the attribute field and convert it to number.
3. save changes return to data view and add calculate column to calculate the value.
Column = IF ( [Risk Adjusted] = "No", [Value] / [Incrementality %], IF ( [Attribute] > 2020, [Value] / [Incrementality %] * .5, IF ( [Attribute] = 2020, [Value] / [Incrementality %] * .75, [Value] / [Incrementality %] * .9 ) ) )BTW, I haven't found the 'Risk Adjusted' fields in your table, can you please explain more about this?
Regards,
Xiaoxin Sheng
HI Anonymous,
Can you please share some dummy data with the same data structure? It is hard to test your code without any sample data.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hello Anonymous
thanks a lot for your quick reply.
Essentially, I have a big Masterfile with data from all regions, looking at Projects, Volume Data, System Net Revenue, etc.
For easier sharing, this is a simplified version of what my excel table looks like with some dummy data:
Region | Project Name | Launch Date | Incrementality % | System NR 2020 | System NR 2021 | System NR 2022 |
EU | Project 1 | 01 August 2020 | 100% | € 3,000.00 | € 3,200.00 | € 3,400.00 |
EU | Project 2 | 01 April 2022 | 40% | € 2,000.00 | € 2,200.00 | € 2,400.00 |
EU | Project 3 | 01 September 2021 | 40% | € 1,200.00 | € 1,400.00 | € 1,600.00 |
EU | Project 4 | 01 August 2022 | 40% | € 4,000.00 | € 4,200.00 | € 4,400.00 |
EU | Project 5 | 01 April 2022 | 40% | € 1,000.00 | € 1,200.00 | € 1,400.00 |
EU | Project 6 | 01 March 2020 | 40% | € 3,000.00 | € 3,200.00 | € 3,400.00 |
EU | Project 7 | 01 June 2020 | 40% | € 2,000.00 | € 2,200.00 | € 2,400.00 |
EU | Project 8 | 01 August 2021 | 40% | € 1,200.00 | € 1,400.00 | € 1,600.00 |
EU | Project 9 | 01 April 2020 | 40% | € 4,000.00 | € 4,200.00 | € 4,400.00 |
EU | Project 10 | 01 September 2020 | 50% | € 1,000.00 | € 1,200.00 | € 1,400.00 |
EU | Project 11 | 01 August 2022 | 50% | € 3,000.00 | € 3,200.00 | € 3,400.00 |
EU | Project 12 | 01 April 2020 | 50% | € 2,000.00 | € 2,200.00 | € 2,400.00 |
EU | Project 13 | 01 March 2021 | 50% | € 1,200.00 | € 1,400.00 | € 1,600.00 |
EU | Project 14 | 01 June 2020 | 50% | € 4,000.00 | € 4,200.00 | € 4,400.00 |
EU | Project 15 | 01 August 2022 | 50% | € 1,000.00 | € 1,200.00 | € 1,400.00 |
EU | Project 16 | 01 April 2020 | 50% | € 3,000.00 | € 3,200.00 | € 3,400.00 |
EU | Project 17 | 01 September 2020 | 50% | € 2,000.00 | € 2,200.00 | € 2,400.00 |
EU | Project 18 | 01 August 2022 | 50% | € 1,200.00 | € 1,400.00 | € 1,600.00 |
EU | Project 19 | 01 April 2020 | 50% | € 4,000.00 | € 4,200.00 | € 4,400.00 |
EU | Project 20 | 01 August 2021 | 50% | € 1,000.00 | € 1,200.00 | € 1,400.00 |
EU | Project 21 | 01 April 2021 | 55% | € 3,000.00 | € 3,200.00 | € 3,400.00 |
EU | Project 22 | 01 September 2020 | 60% | € 2,000.00 | € 2,200.00 | € 2,400.00 |
EU | Project 23 | 01 August 2022 | 60% | € 1,200.00 | € 1,400.00 | € 1,600.00 |
EU | Project 24 | 01 April 2020 | 65% | € 4,000.00 | € 4,200.00 | € 4,400.00 |
EU | Project 25 | 01 March 2021 | 65% | € 1,000.00 | € 1,200.00 | € 1,400.00 |
Since our reporting is moving from Tableau to PowerBi, I’m trying to build all the dashboards and reports as we could do it in our old system as well.
I managed to do most of them, however this is one of the most crucial ones I’m missing, so I wanted to reach out for help.
Please let me know if you need anything else.
Kind regards,
Silvia
HI Anonymous,
You can refer to the following steps if they are suitable for your requirements.
Steps:
1. do 'unpivot columns' on 'system nr' fields to convert them to attribute and value.
2. remove 'system nr' from the attribute field and convert it to number.
3. save changes return to data view and add calculate column to calculate the value.
Column = IF ( [Risk Adjusted] = "No", [Value] / [Incrementality %], IF ( [Attribute] > 2020, [Value] / [Incrementality %] * .5, IF ( [Attribute] = 2020, [Value] / [Incrementality %] * .75, [Value] / [Incrementality %] * .9 ) ) )BTW, I haven't found the 'Risk Adjusted' fields in your table, can you please explain more about this?
Regards,
Xiaoxin Sheng