Calculate Total Availability per Line Weekly

[Proday83]

Hello everyone,

I have a new challenge ahead of me...right now in excel we have daily imput of issues on Line1 and Line2 (line column below). I need to present Total Availability on a Weekly bases in Line chart.

Excel daily data

From here I siccided to retireve all data on a daily level (planned production time is same daily for a specific Line so I could use Min or Max) to subtract sum of Total Lost Product time from Daily Planned (but on a weekly bases is problem for me, because every day can have different planned time). Formulas I used below.

Planned Production Time 

Sum of Total Lost Line1 

Total Availability Measure 

So if I had possibility to add Average on Total Availability Measure it would be great. Other way I just need help with formula (mby with propper DAX formula, I can retrieve values for days and weeks in one, but not certain). Below is what I'm getting in BI and what I need.

PowerBi Matrix check 

Thanks a lot For Help

EDIT: Sorry, I have some issues with embeding photos directly to be visable.

[Proday83]

First of all, I want to thank you both. Practically, both solutions get the job done for me.

@Daniel29195 this solution with sumx is what I needed at start, It solves "mby with propper DAX formula, I can retrieve values for days and weeks in one, but not certain" this part. Now I need one measure less to get the result 🙌

@Ashish_Mathur If I underestood correctly, basically you did this "So if I had possibility to add Average on Total Availability Measure it would be great" with your Measure = averagex(values(Calendar[Date]),[Total availability Daily Line1])!! Thanks a lot man, you have learned me a really important thing 👍

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Try this measure

Measure = averagex(values(Calendar[Date]),[Total availability Daily Line1])

Hope this helps.

[Daniel29195]

Hello

it seems the number you want to achieve is equal to ( lets say Wk45) , the( sum (of planned production Timeline1) - sum ( total line 1 lost production time )) / sum (of planned production Timeline1)

to solve your problem try this : 

planned production time line 1 =  replace min with sum () , 

this way you will have : 

at the wk45 : 

((510 + 510 + 510 + 450 )  - 165)  /  (510 + 510 + 510 + 450 )  =  91.5%

hope this helps

[Daniel29195 · Accepted solution]

if you need the sum of minimum, 

then change planned production time measure to  :  

sumx (

values ( date ) ,

calculate (

min ( failure_rates_data[planned production time]),

failure_rates_data[line] = "Line1"
)
)