Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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) From this table, I need to calculate total hours per Project_ID ba...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    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.

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-eachen-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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.