Forum Discussion
Risk Adjusted Calculations
Hi All!
So maybe let me set the context: I started a new position to do revenue reporting for my company and for this, I started learning DAX since Monday!
I created a few Dashboards by now and managed to write some measures, however there is still one option I want to include which I need help for as it's a little more complex: Risk Adjusted Calculations!
What it means: The further ahead projects are planned, the higher risk factor they're getting assigned as chances are higher that these won't happen.
I tried setting up formulas that read along the lines of "If System Net Rev. is from year x, assign *.5", ..... but didn't have much success.
In our previous reporting system, this is the formula we used:
if [Risk Adjusted] = "No" Then [Gross/Incremental Value] elseif
Year([Launch Date1])>2020 then [Gross/Incremental Value]*.5 elseif
Year([Launch Date1])=2020 then [Gross/Incremental Value]*.75 ELSE
[Gross/Incremental Value]*.9 end
I tried 'translating' this into PowerBi / DAX language, but also failed.
Is there anyone with more in depth knowledge that could possibly help?
Thanks a million!
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
3 Replies
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