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

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft 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's avatar
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's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft 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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks for answer so quickly Phil_Seamark,

       

      Yes, I have a CalendarTable with month, year, ...

      I proved your formula and didn't show me what I need. 

      I think this part don't work fine. It sums all hours of all tasks that I have, the date filter in the part of ALL() don't work.

      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]))
					))

       

      • 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

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous

     

    Do you have a Date Table that has a Month column?