cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
linasster Frequent Visitor
Frequent Visitor

Need help with calculations

Hello,

I am very new in PowerBI and I need help with calculations.

From the table below (this is a small part of the table)

PowerBI.jpg

From this table, I need to calculate total hours per Project_ID based on Task_ID planned hours. The difficult part for me is that Task_ID with the same name should be included once in the hour's calculation for Project_ID. I mean that for instance for Project_1 final result should be 15hours instead of 70hours. And also I need to calculate total progress for the Project_ID issue here is that some fields are missing value. So how can I ignore it or include as 100% in the calculation?

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Need help with calculations

Hi @linasster ,

 

For your first requirement, you need to create two measures.

AVG =
CALCULATE ( AVERAGE ( 'Table'[Planned Hours] ) )
TotalHours =
SUMX ( VALUES ( 'Table'[Task ID] ), [AVG] )

Then you can use Project ID and TotalHours to get a visual.3-1.PNG

For your second requirement, I do not understand the logic of your column “Progress”. Maybe you can provide more information.

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Need help with calculations

Hi @linasster ,

 

For your first requirement, you need to create two measures.

AVG =
CALCULATE ( AVERAGE ( 'Table'[Planned Hours] ) )
TotalHours =
SUMX ( VALUES ( 'Table'[Task ID] ), [AVG] )

Then you can use Project ID and TotalHours to get a visual.3-1.PNG

For your second requirement, I do not understand the logic of your column “Progress”. Maybe you can provide more information.

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
linasster Frequent Visitor
Frequent Visitor

Re: Need help with calculations

Hi @v-eachen-msft 

Thank you very much for your help. Your solution works perfectly.

Now I will try to clarify the second part.

Progress column shows how progress increased after every attempt to the task.

For example for Task_1 in Project_1 final progress is unknown because we are missing value from the last attempt, however, we know that it is not less than 80%.

If we are missing 100% value can we use second-biggest? because here is important the biggest value in progress per task.

Thank you very much.

Have a nice weekend. 

 

Community Support Team
Community Support Team

Re: Need help with calculations

Hi @linasster ,

 

According to your thinking, the method is actually the same as above.

Firstly, you need to create a new column "Progress Hours" to calculate the value of progress.

Progress Hours =
'Table'[Planned Hours]
    * IF (
        'Table'[Progress] <> BLANK (),
        VALUE ( LEFT ( 'Table'[Progress], LEN ( 'Table'[Progress] ) - 1 ) ) / 100,
        0
    )

Create two measures.

MAX =
CALCULATE ( MAX ( 'Table'[Progress Hours] ) )
TotalProgressHours =
SUMX ( VALUES ( 'Table'[Task ID] ), [MAX] )

Then you can get the final visual with Project_ID and TotalProgressHours.Capture.PNG

 

Best Regards,

Eads

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 40 members 989 guests
Please welcome our newest community members: