Calculate efficiency using two tables

[Hongmingk]

Hi all,

I have two tables which have a relationship using weeks columns. RSZA0003_available Hour is my machine weekly available hours

In Sizing_Machine table, i have a measure name 'Runtime" now i would like to see the efficency of my machine over the past few weeks. I've tried to use the following code to calculate. But for some reason when i use related function, power bi didnt recognise the column. 

Efficencey = VAR runtime = [Run Time in Minute]

var _hour = RELATED('RSZA0003_Avaiable Hour'[Total available Hour])

var efficency_1 = runtime / _hour

    return efficency_1

May i ask for some advice? Thanks in advanced.

[Anonymous]

Hi @Hongmingk 

I would like to apologize for the belated reply.

Have you solved your problem? If not, here is my suggestion: You could try changing the RELATED formula to a SELECTEDVALUE formula.

For more details, you can read related document link: SELECTEDVALUE function - DAX | Microsoft Learn

Since I can't see your screenshot clearly, I apologize for not being able to reproduce your formula. If your problem persists, you can refer to the following link to provide example data and your expected results:  How to provide sample data in the Power BI Forum - Microsoft Fabric Community. We can better understand the problem and help you. Please remove any sensitive data in advance. 

Best Regards,
Community Support Team _Yuliax

[Daniel29195]

hello @Hongmingk 

to use related, you need an iterator such as sumx, max, minx, filter, addcolumns , .... .

assuming that the total available hour is additive across dimensions,  i guess  you can simply use : 

Efficencey = 

VAR runtime = [Run Time in Minute]

var _hour = sum('RSZA0003_Avaiable Hour'[Total available Hour])

var efficency_1 = runtime / _hour

    return efficency_1

let me know if you need any help 

best regards,

[Hongmingk]

Hi,

Thanks for the help and really appericated. But unfortunately the answer is not im looking for. I'm sorry for not being clear on my issue. Let me elobrate. 

The picture below show the RSZA0003_Avaiable Hour'

If i use sum('RSZA0003_Avaiable Hour'[Total available Hour]). The result will be wrong. As you can see in the picture below. 

By right, if i want to calculate the efficency, i would need to use runtime / total available hour. Hence i should have the result as below 

Week 1 : 0.565

Week 2 : 0.589

Week 3 : 0.528

As you can see in the picture below, the "Total avaiable hour" is straight away input into the table. This two table RSZA_avaiable hour and sizing machine have a relationship using weeks. 

Thanks in advanced and sorry for the trouble.

[ValtteriN]

Hi,

To make RELATED work in a measure you need to use some table manipulating function E.g.

Measure 14 =

var _vtable = ADDCOLUMNS('Table (16)',"A",RELATED('Calendar'[Year])) RETURN

MAXX(_vtable,[A])

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/