Monthly Percent in a Table

Hello everyone,

I´m having problems with a DAX calculation.

I need to calculate the % of hours that suppose every row per month and person and I have a table like this.

Any help?

Thanks.

Need Help

v-ljerr-msft

9 years ago

Hi Anonymous,

Could you try the formula below to see if it works in your scenario? :smileyhappy:

Task % =
VAR currentMonth =
    MONTH ( MAX ( 'Table2'[Fecha de trabajo] ) )
VAR currentYear =
    YEAR ( MAX ( 'Table2'[Fecha de trabajo] ) )
VAR currentPerson =
    MAX ( 'Table2'[Id_Persona] )
VAR HoursInTask =
    CALCULATE (
        SUM ( 'Table2'[Horas] ),
        ALLEXCEPT ( Table2, 'Table2'[Id_Proyecto] )
    )
VAR AmountHoursPersonInAllTasks =
    CALCULATE (
        SUM ( 'Table2'[Horas] ),
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[Id_Persona] = currentPerson
                && (
                    MONTH ( 'Table2'[Fecha de trabajo] ) = currentMonth
                        && YEAR ( 'Table2'[Fecha de trabajo] ) = currentYear
                )
        )
    )
RETURN
    DIVIDE ( HoursInTask, AmountHoursPersonInAllTasks )

Regards

7 Replies

Phil_Seamark
Microsoft Employee
9 years ago
Hi Anonymous

Could you please post a mock up of what you expect your result to look like. That will help us fill the gap.
- Anonymous
  Not applicable
  9 years ago
  Hello Phil_Seamark,
  
  I need a formula that looks like:
  Task% = [Hours in the task]/[Amount of worked Hours in the month of this person in all worked tasks]
  - Phil_Seamark
    Microsoft Employee
    9 years ago
    Hi Anonymous
    
    This might be getting close
    
    Task % = var HoursInTask = CALCULATE(SUM('Table2'[Horas]),ALLEXCEPT(Table2,'Table2'[Id_Proyecto])) var AmountHoursPersonInAllTasks = CALCULATE( SUM('Table2'[Horas]), FILTER( ALL('Table2'), MONTH('Table2'[Fecha de trabajo])= MONTH(MAX('Table2'[Fecha de trabajo])) && YEAR('Table2'[Fecha de trabajo])= YEAR(MAX('Table2'[Fecha de trabajo])) )) RETURN DIVIDE(HoursInTask,AmountHoursPersonInAllTasks)

Forum Discussion

Monthly Percent in a Table