Dynamically calculating column in a table based on slicer value

[Anonymous]

Hi all,

In one of my tables I have a list of IT systems, and a column that lists, for each system, the amount of hours said system has been down or non-functional. I'm tryinng to calculate the proportion of a selected period of time that a given system was down for.

For example, if system X was down for 12 hours on the 3rd of March:

- There are 744 hours in March, so if I selected March in a slicer I would like to display that it was down for 1.612%

- There are 2160 hours in the first quarter of the year, so if i selected Q1, I'd like to display that it was down for 0.56%

- There are 8760 hours in a year, so if i selected a whole year I'd like to display that it was down for 0.14%

I am using a date table that has a column for hours, which has a value of 24 in every row. (I didn't allow for daylight savings etc as that level of accuracy wasn't necessary). When I display the amount of hours I use SUM(Hours).

My current formula is %Downtime = Downtime / SUM('Date'[Hours]). At the moment, I have 3 years worth in my date table and no matter what date range I select in the report, every %Downtime value is shown as "Downtime / 26,280".

What do I have to do to get this value to change based on the date range I select?

Thanks 🙂

[SergioSilvaPT]

Hi @Anonymous ,

Can you show us your model? Is the Calendar table connected to the table from were you get the downtime values?

[Anonymous]

 

Yes, the Date Table is directly related to the table that has the downtime values

[SergioSilvaPT · Accepted solution]

From the image looks like you have created an calculated column, and i would recomend to change that to a measure. 
Also, i have made a little test on my side and it should work like this:

% Downtime = [Downtime] / (SUM('Calendar'[Day]) * 24)
(considering the [Downtime] is a measure in hours)

and it works with day, months or even years...

Regards,

Sérgio Silva 

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

[Anonymous]

How is that 'Day' column formatted in your model? 

Also could you show me how you made your 'Downtime' measure?

Also, I think the fact that it looked like a calculated column was an error on my part. See here:

[Anonymous]

Never mind, I figured that part out. It works now, Thanks!

[SergioSilvaPT]

Glad i could help!