Divide two fields from different table to calculate rentability

[Quenjo]

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

[Thejeswar]

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]

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 · Accepted solution]

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]

Hi @Thejeswar 

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