Forum Discussion
Divide two fields from different table to calculate rentability
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 )
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,
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
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 )