Forum Discussion
Rate between tables
Hi Ashish,
Looking at your file, you have calculated the rate as the ration between cycles and hours. That's not what I need to calculate. Apologies if I wasn't clear.
What I need to do is calculate the rate as:
- Rate = number of failure events / utilization (hours)
or
- Rate = number of events / utilization (cycles)
depending whether the failure is an hydraulic failure, or electrical failure. The rates need to be operator based, which means I want to be able to select the operator from a data slicer, and the rates of that operator only should show up. Also the rates need to be calculate for every month based on how many failures an operator had in a particular month, and the utilization for that month.
What I've done so far is create a measure as:
Rate = DIVIDE (
COUNTROWS ( 'Table 1' ),
SUMX (
FILTER (
'Table 2',
'Table 2'[Operator] = MAX ( 'Table 1'[Operator] )
&& 'Table 2'[Date] = MAX ( 'Table 1'[Date] )
),
'Table 2'[Monthly utilization (hours)]
))
What that does is calculate the rate for every month. That's fine.
What I need to do now is being able to utilize a slicer to select a data range, for instance 4-month range, and I'd like the "Rate" to be shown as the average of the rates calculated in those 4 months. At the moment if I select a 4-month range with the data slicer, the "Rate" will simply show the sum of the 4 rates and not the average.
Other problem, if for a particular month there are no failures, "Rate" will simply not consider that month. What I'd like to do is have a 0 in that month, instead of not having that month at all.
I hope that's more clear? Thanks!
Hi,
So, if it is a hydraulic failure, then the denominator should be utilisation (hours). For any other failure, it should be utilisation (cycles). Am i correct?
Yes, you're correct. Maybe I can create different measures for each type of rate, i.e. 1 hydraulic rate (hours), 1 electric rate (cycles), etc...
My biggest struggle is how to calculate the average of each rate using data slicers for operator, date range an type of failures, since these measures for rates don't exist for months when there are no events (instead of having zeroes in those months). So any average would be incorrect.
Hi Dale,
I've uploaded a dummy file here. My end goal is to have a gauge where if I select a data range, operator and type of failure the value will be the average of the rates for all the months. included in the data range I selected. At the moment the value shown by the gauge is simply the sum of the rates, and not the average. As I understand it, there are currently 2 issues:
1- value is a total and not the average of the monthly rates
2- even if the value was an average, since the Rate measure doesn't return any value for months where there were no failure events, the average would be wrong. I need Rate measure to return "0" every month when there are no failure events.
Thank you!
Hi,
Please share the download link of the Excel file where you have all Tables.