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,