Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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? T...
Need Help
  • v-ljerr-msft's avatar
    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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks v-ljerr-msft,

Works great with little modifications.

Task % =
VAR currentMonth =
    MONTH ( 'Table2'[Fecha de trabajo] )
VAR currentYear =
    YEAR ( 'Table2'[Fecha de trabajo]  )
VAR currentPerson =
    ( 'Table2'[Id_Persona] )
VAR HoursInTask =
     SUM ( 'Table2'[Horas] )
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 )