Forum Discussion

Quenjo's avatar
Quenjo
Icon for Helper II rankHelper II
3 years ago
Solved

Divide two fields from different table to calculate rentability

Hi community,

 

I have the following situation :

 

 

First column here represents a task, second column the effective time spent on the task and the third column represents the time that we planned for all the tasks.

 

How can I add a new column which is a percentage and calculate the "61.07 / 57" ?

 

It seems very simple but I have some problems :

 

The second column comes from the "Task" table, which has a custom calculated field :

 

duration = HOUR([time_worked]) + MINUTE([time_worked])/60 + SECOND([time_worked])/3600
 
The third column comes from the "Project" table and is a constant. In this example, 57.
 
I am trying to add a column in the project table that does :
 
rentability = Task[time_worked] / project[forfait]
 
But I can't get the "Task[time_worked]" field, which comes from the other table.
 
The relationships are like this :
 

 

Where change_task is "Task" in my example and "otp" is "Project".

 

How can I solve my issue ?

 

Let me know !

 

Many thanks,

Jonathan

 

Need Help
  • Thejeswar's avatar
    Thejeswar
    3 years ago

    Hi Quenjo ,

    Try using it this way

    Rentabilité(100%) = 

VAR resultat = IF (ISBLANK(SUM('change_task'[duration])), BLANK(), DIVIDE(SUM('otp'[Forfait du projet(H)]), SUM('change_task'[duration])) -1 )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( change_task[number] ), BLANK (),
        ISINSCOPE ( otp[u_number] ), resultat,
        ISINSCOPE ( company[name] ), resultat,
        resultat
    )

4 Replies

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    3 years ago

    Hi Quenjo ,

    If you want to create a column, you might have to use the RELATED(). This Column you can create in the Task table

    rentability = RELATED(Task[time_worked]) / project[forfait]

     

    If creating the column in the Project table, then try using the below DAX

    rentability = Task[time_worked] / SUMX(RELATEDTABLE(project), project[forfait])

     

    Alternatively, You can achieve this by creating a measure instead. But you might have to use an appropriate aggregation function in the measure. In this case, since this is time, I am using MAX() function

    rentability = MAX(Task[time_worked]) / MAX(project[forfait])

     

    Haven't tried any of these since not having sample data, hopefully they work

     

    Regards,

    • Quenjo's avatar
      Quenjo
      Icon for Helper II rankHelper II
      3 years ago

      Hi Thejeswar,

       

      I managed to achieve this by adding a new measure to the "Project" table and doing the following :

      Rentabilité(100%) = 

VAR resultat = DIVIDE(SUM('otp'[Forfait du projet(H)]), SUM('change_task'[duration]))
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( change_task[number] ), BLANK (),
        ISINSCOPE ( otp[u_number] ), resultat,
        ISINSCOPE ( company[name] ), resultat,
        resultat
    )

       

      Now, I have the following and this is great !

       

      However, here my measure is calculating 51.83 / 61.07, which in percentage gives 84.87.

       

      In my case, I want to calculate the rentability, so I should substract "1" to my measure.

      When trying to substract something to my measure, I have the problem that it puts "-100%" even where I do not want to ! In all the records :

       

      Rentabilité(100%) = 

VAR resultat = DIVIDE(SUM('otp'[Forfait du projet(H)]), SUM('change_task'[duration])) -1 
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( change_task[number] ), BLANK (),
        ISINSCOPE ( otp[u_number] ), resultat,
        ISINSCOPE ( company[name] ), resultat,
        resultat
    )

       

       

      How can I avoid this by using a measure ? Please, let me know.

       

      Many thanks,

      Jonathan

      • Thejeswar's avatar
        Thejeswar
        Icon for Super User rankSuper User
        3 years ago

        Hi Quenjo ,

        Try using it this way

        Rentabilité(100%) = 

VAR resultat = IF (ISBLANK(SUM('change_task'[duration])), BLANK(), DIVIDE(SUM('otp'[Forfait du projet(H)]), SUM('change_task'[duration])) -1 )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( change_task[number] ), BLANK (),
        ISINSCOPE ( otp[u_number] ), resultat,
        ISINSCOPE ( company[name] ), resultat,
        resultat
    )