average of production time for 1 ID

[Tommyvhod]

Hi all

I would like to create a Dax formula for average production time for a product. I have different working stations and for 1 product 1 name and for one piece unique IDs. I would like to add a slicer as well for the finish time of the part.

So I would like to filter out IDs which has the first and last working stations not blank ( so it is completely went trought the production) and from that sum of production time create an average.

so. eg I have 4 working stations and I would like to filter out IDs which contains the first and fourth Working stations.

Working station	item	ID	time
1	p	112	0,1
1	o	113	0,5
1	l	114	0,3
1	o	115	0,5
2	p	112	0,3
2	o	115	0,5
2	l	114	0,3
3	p	112	0,3
3	o	115	0,5
4	o	115	0,5

In this case item o with ID 115 is completely finished because operation 1 and 4 is finished. I would like to sum up the production time ( in this case 4 x 0.5) for all finished parts and create an average for item o ( of course I have more pcs in production from one item )

The trick just to make it more complicated; I would like to filter the amounts in time ranges. I used to filter out last 5 weeks production but the full production time can be longer than 5 weeks. So I would like to filter last work station by time but leave the full process time from the beginning.(I have another column for dates as well for each working station.)

The formula should find all finished item ( sliced with last operation date ) filter out items which do not contain operation 1 and 4 ( but of course work station 1 can be a year before works station 4 but i need the full production time from the beginning.) sum up one IDs prouction time and average it for one item. I hope you guys understand what is my goal

I tried multiple approaches but this is way over my knowledge. Any help is appreciated

Thank you

[JustJan]

Hi @Tommyvhod ,

If I understood your question you can do the following: 

( I have added a ProductionDate to you data table)

So only the last 5 weeks are selected.

The measure first finds the ID where the Workstation = 1 and 4 and then calculates the production time.

WS 1&4 Production Time = 
IF (
  CALCULATE( 
     SUMX( Production, IF (Production[Working station] = 1 || Production[Working station] = 4, 1)),
     All(Production[ProdDate])
    ) = 2, 
   CALCULATE (
    sum(Production[time]), -- change sum to average for the average production time
     All(Production[ProdDate])
) 
)

Hope you this helps

Jan 

[Tommyvhod]

I added the formula but I receive a blank result. I tried work station as a value but stil nothing

sum of act = IF(

CALCULATE(

SUMX(Data;IF(Data[Operacia] = VALUE(30)|| Data[Operacia] = VALUE(410);1));

ALL(Data[Datum transakcie]))=2;

CALCULATE(

SUM(Data[Act time]);ALL(Data[Datum transakcie])))

(The operacia is the working station,i have 70 work stations starting with number 30 and ending 410; datum is date

I dont understatnd the =2 marked with red, whgat that does meant to say? ( I am not a dax expert )