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 w...
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
    )
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
    )
      • Quenjo's avatar
        Quenjo
        Icon for Helper II rankHelper II
        3 years ago

        Hi Thejeswar 

         

        This is exactly what I was searching ! Thank you a lot for the help provided 🙂