Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DannyDicaprio
New Member

Hep with the formula

@v-yaningy-msft 

 @v-yangliu-msft

@CarlossSainz 

 

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 6
lbendlin
Super User
Super User

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-...

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/1447...

RisknameControlsGross_risk_potentialActual_risk_reductionGross_potential_burndownActual_risk_burndown
Vendor-service transitionsA£10£105010
Vendor-service transitionsB£20£5455
Vendor-service transitionsC£30£5400
Unavailability of IT systems & servicesD£10£105010
Unavailability of IT systems & servicesE£20£5455
Unavailability of IT systems & servicesF£30£5400

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

 

lbendlin_0-1713729521360.png

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. 

 

isknameControlsGross_risk_potentialActual_risk_reductionCompleted date Gross_potential_burndown_remainingActual_risk_burndown
Risk 1A£10£504-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 1B£20£1004-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 1C £5 £230-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 2D£10£504-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 2E£25£1030-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 2F £15 £430-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

 

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.

Can you please help me figure out how to get the 1st point values in a formula rather than in a visual. I need a column of those calculations so that i can try to figure out the second part of the problem. Im not able to get the calculations in a column using any formula.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.