SteveM
New member
- Joined
- Oct 31, 2024
- Messages
- 1
- Reaction score
- 0
- Points
- 1
- Excel Version(s)
- Office 365
I have uploaded a sample file with 3 tables in the data model, a calendar table, a table called Units and a table called Events.
The units table represents a list of all units in a fleet of equipment with the dates they first went online, and if they were decommissioned then also the date of decommissioning. The Events table lists all the outage events occurring in the fleet with a start date and end date of the outage.
It is a simple task with a pivot table to chart the raw outage count over any time period, but what we want to do is to normalize that number of outages by dividing it by the number of Units that were operational in the fleet for the time period selected by the user (slicer).
As an example, if the user selects a particular one month period with the slicer and there were 3 units operating for the whole month, and 1 unit came online in the middle of the month, say on the 15th, we would calculate that as 3.5 operating units for that one month time period. ((3 x 30)+(1 x 15))/30 = 3.5
I could then take that number and, say if there were 7 outages during that month, Icould chart that as 2 outages per operating unit.
My question is how to create that measure that loops thru the Calendar table, aggregating the number of operating units on each date and then divides it by the number of days to get this value. It will be some sort of union of between the Calendar table and the Units table perhaps? Any help is appreciated.
Thanks,
Steve
The units table represents a list of all units in a fleet of equipment with the dates they first went online, and if they were decommissioned then also the date of decommissioning. The Events table lists all the outage events occurring in the fleet with a start date and end date of the outage.
It is a simple task with a pivot table to chart the raw outage count over any time period, but what we want to do is to normalize that number of outages by dividing it by the number of Units that were operational in the fleet for the time period selected by the user (slicer).
As an example, if the user selects a particular one month period with the slicer and there were 3 units operating for the whole month, and 1 unit came online in the middle of the month, say on the 15th, we would calculate that as 3.5 operating units for that one month time period. ((3 x 30)+(1 x 15))/30 = 3.5
I could then take that number and, say if there were 7 outages during that month, Icould chart that as 2 outages per operating unit.
My question is how to create that measure that loops thru the Calendar table, aggregating the number of operating units on each date and then divides it by the number of days to get this value. It will be some sort of union of between the Calendar table and the Units table perhaps? Any help is appreciated.
Thanks,
Steve