Risk Adjusted Calculations

[Anonymous]

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

[Anonymous]

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

[Anonymous]

 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

[Anonymous · Accepted solution]

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.

Unpivot columns (Power Query) 

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