Forum Discussion
Hep with the formula
Super UserPlease provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
| Riskname | Controls | Gross_risk_potential | Actual_risk_reduction | Gross_potential_burndown | Actual_risk_burndown |
| Vendor-service transitions | A | £10 | £10 | 50 | 10 |
| Vendor-service transitions | B | £20 | £5 | 45 | 5 |
| Vendor-service transitions | C | £30 | £5 | 40 | 0 |
| Unavailability of IT systems & services | D | £10 | £10 | 50 | 10 |
| Unavailability of IT systems & services | E | £20 | £5 | 45 | 5 |
| Unavailability of IT systems & services | F | £30 | £5 | 40 | 0 |
In tha above table, i have gross potential for each controls and each control is mapped against different risks. I just gave an example of two risks here but i have multiple risks in the original data. I want to create a single formula for the burndown columns
For that,
1. I have to add up the sum of gross_risk_potential and actual_risk_reduction for each of the risk separately
2. There should be cumulative reduction of the values from the sum. For eg- Vendor-service transitions risk has the total sum of 60. Control A has gross potential of 10. The gross_potential_burndown should be 50. For control B it would be 50(previous value)-20(present value)=30
Basically i want all the above 2 points i.e summing up gross_risk_potential and actual_risk_reduction for each of the risk separately and doing cumulative subtraction for each row.
The closest formula i got was calculating the sum value for each risk separately which implies creating multiple columns and doing the cumulative subtraction. Is there a way easier than this?
- lbendlin
The first item is simple
For your second item you need to provide a sort order. Is it supposed to be alphabetic by Controls?
Not sure what the expected outcome should look like for point 2.
Hi,
1. Can you please tell me what will be the calculation for the summing up which you did in the above picture?
2. I have given the expected outcomes in the last two columns of the table- Gross risk burndown and actual risk burndown.
iskname Controls Gross_risk_potential Actual_risk_reduction Completed date Gross_potential_burndown_remaining Actual_risk_burndown Risk 1 A £10 £5 04-04-2024 35-20=5
35=sum of gross risk potential based on the risk name till time period(04-04-2024)
20=gross risk potential for control A and control B as the completion date is same for both the controls
35-15(10+5)=20 Risk 1 B £20 £10 04-04-2024 35-20=5
35=sum of gross risk potential based on the risk name till time period(04-04-2024)
20=gross risk potential for control A and control B as the completion date is same for both the controls
35(gross risk potential)-15(10+5)=20
35=total sum of
Risk 1 C £5 £2 30-04-2024 5-5=0
1st 5=last gross risk potential before the completion date(30-04-2024)
2nd 5=gross risk potential for control C
35-17(5+10+2)=18 Risk 2 D £10 £5 04-04-2024 50-10=40
50=total sum of sum of gross risk potential based on the risk name
10=gross risk potential for control D until the time period.
40 Risk 2 E £25 £10 30-04-2024 40-40=0
40= Total sum of gross risk potential until the time period(30-04-2024)
40=sum of gross potential for controls E and F as the completion date is same for both of them
35 Risk 2 F £15 £4 30-04-2024 40-40=0
40= Total sum of gross risk potential until the time period(30-04-2024)
40=sum of gross potential for controls E and F as the completion date is same for both of them
35 I want the last two columns, gave a desirable output along with the calculation. Based on the last two columns i want to create a burndown for the risks on how they are reducing on a regular period with the implementation of controls
- lbendlin
1. I used implicit measures directly in the visual.
2. I am very much lost, I don't understand a single one of these calculations. I hope someone else can help you further.
