Forum Discussion
Hep with the formula
Anonymous
Anonymous
Hi
I have a formula below for calculating the sum of gross risk potential in the table. I want the sum calculation based on the risk name instead of summing up the entire column. I tried the below formula but it is not giving the correct value.
I can give an example
The total sum of gross risk potential is 100. But the gross risk potential for risk A is 20. I want to get this separate value for all the risks i have in a single column without creating multiple columns.
TotalRiskPotentialPerRisk =
SUMX(
VALUES(Top_Risk_Controls[Riskname]),
CALCULATE(
SUM(Top_Risk_Controls[Gross_risk_potential]),
Top_Risk_Controls[Riskname] = EARLIER(Top_Risk_Controls[Riskname])
)
)
In the above formula, im getting the same values as the gross risk potential column instead of the sum of them based on the risk name. How to get the sum of the values based on the risk name.
Please note that the data source is the sharepoint list.
6 Replies
Please 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/1447523Riskname 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?
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.
