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,
SilviaHI 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