Search code examples
datepowerbidaxmeasure

How to calculate the percentage a machine is online

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:

enter image description here

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

Solution

  • 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])