How to calculate the percentage a machine is online

Question

Dear Power BI Community,

I am having trouble calculating the percentage a piece of equipment is online. My data consist of columns of machine specifics and a row for each day equipment is offline. My calculation steps to create a Measure that calculates percentage of the online time:

I successfully calculated the number of days in a month:

Days per month = calculate(count(TD_Date[Date].[Day]);TD_Date[Year]="2020")
// CALCULATE THE NUMBER OF DAYS IN EACH MONTH OF THE YEAR

I successfully calculated the number of days a machine is offline in a month:

Count the dates a machine is out of order: TF_Eventos[TimeSpan Date out of Order]

Then I try to calculate the online time:

 % Online = 1-DIVIDE(COUNT(TF_Eventos[TimeSpan].[Date]);[Days per month])

// CALCULATE PERCENTAGE ONLINE

This does not give the wanted result:

Wanted result:

As can be seen in the screenshot and the file, it is not calculating correctly the online time EG: Machine 10000189 is two days out of order in january and 0 in feb, january has 31 days, the time that the machine is online in January should be 93.54% and in feb 100%. However, this is not shown in the graph and it shows the same value for all rows (machines..).

My pbix File:

https://drive.google.com/file/d/1by_Ovp2qUPn9g94wPbo3WKcqWj4rPRlg/view?usp=sharing

What am I missing? Any hellp is very much apreciated!

Regards,

Stijn Hillenius

Hi Stijn, you said that it does not give the wanted result, but could you tell us what the wanted result is? What went wrong and what is what you expect? — Steven
Dear Steven, thank you for your reply. As can be seen in the screenshot and the file (just added), it is not calculating correctly the online time EG: Machine 10000189 is two days out of order in january and 0 in feb, january has 31 days, the time that the machine is online in January should be 93.54% and in feb 100%. However, this is not shown in the graph and it shows the same value for all rows (machines..). Thx for the help! — Stijn Hillenius
Hi Stijn, thanks for expanding the explanation. I cannot give a direct answer to the problem myself, since I don't know powerbi, but I think you should take a proper look why the result is always 0,98%, there's probably something wrong that it keeps repeating itself, this doesn't seem to happen when you calculate the date out of order, so try looking for the differences between them. — Steven
Dear Steven, Thank you for your reply. Obviously, there is something wrong, thats why i posted the question and indicated the result as not wanted. Unfortinately, i am unable to solve the problem myself, and therefore,I am reaching ou to the community for help. Hope anyone can help me or point me in the right direction! — Stijn Hillenius

Stijn Hillenius Stijn Hillenius · Accepted Answer · 2020-03-04T14:30:54

FYI: I solved the problem. I verified step by step if all my measures gave the result as expected. I found out that the amount of days in a month was not calculated correctly and therefore, messing up my result. This did the trick:

Days per month = calculate(count(TD_Date[Date].[Date]))

TimeSpanDays = COUNTX(TF_Eventos;TF_Eventos[TimeSpan].[Date])

Available Time = [Days per month] - [TimeSpanDays]

% Online = DIVIDE([Available Time]; [Days per month])

How to calculate the percentage a machine is online

1 Answers