Line Efficiency DAX formula

[akwang]

Hi I need help with a formula below:

This is what i want

	Total KG/h Actual	Total KG/h Standard	Production Efficiency %
Line 1	240	250	96%
Line 2	273	300	91%
Line 3	116	120	97%
Line 4	42	50	84%

How can i create a formula that would result in the Production Efficiency % column? When i Sum the Actual it remains according to line, but when i sum the standard i get 720 as all the standard rather than as it is on the table.

[SivaMani]

Hi @akwang,

Create a calculated column with Divide function.

Production Efficiency % = DIVIDE(Table1[Total KG/h Actual],Table1[Total KG/h Standard],0)

Then Change the Format to Percentage which under modeling tab

regards,

Siva

[akwang]

Hi Ihave the data in two separate tables. One with the actual prod and the other with the Standard for Line 1,2,3.

How would i calculate that?

[SivaMani]

 @akwang,

Sorry, I thought you have the values in the same table

Create a relationship between the tables based on the Line.

Then create a calculated measure,

Production Efficiency % = DIVIDE(SUM(Table1[Total KG/h Actual]),SUM(Table2[Total KG/h Standard]),0)

[akwang]

Hi This is what i get on the table visual and my reference table is below:

TABLE VISUALREF TABLE

The STD is not sepatating by line causing incorrect %efficiency

[SivaMani]

What was the procedure you followed to get that output?  

[akwang]

the same formula you told me to use.

[SivaMani]

@akwang,

How did you calculate Line Prod and Total line Prod?

Where it comes from?

could please share some sample data? pbix file would be great

[akwang · Accepted solution]

Hi SiviMani,

I fixed the problem by enabling relationships as both direction relationship.