Forum Discussion
Calculate Total Availability per Line Weekly
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.
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.
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.
Thanks a lot For Help
EDIT: Sorry, I have some issues with embeding photos directly to be visable.
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"
)
)
5 Replies
Hi,
Try this measure
Measure = averagex(values(Calendar[Date]),[Total availability Daily Line1])
Hope this helps.
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
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"
)
)
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 👍You are welcome.
