Need help with calculations

[Anonymous]

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)

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.

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

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.

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.

[Anonymous]

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. 

[v-eachen-msft]

Hi @Anonymous ,

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.

Best Regards,

Eads

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