Forum Discussion
Rate between tables
Hello! I'm quite new to PBI and I'm trying to resolve this issue. It'd be great if any of you could help.
I have two tables setup as below, and I need to create a visual to show the failure rate according to different parameters.
Example:
- Brake system Failure Rate of operator A for Ferrari in Apr 2017 = number of brake system failures / number of hours
- Pneumatic system Failure Rate of operator B for Porche in June 2017 = number of pneumatic system failures / number of cycles
So far I've created a measure called "Count" as Count = CALCULATE(COUNTROWS(),ALLEXCEPT('Table 1','Table 1'[OPERATOR],'Table 1','Table 1'[Date],'Table 1','Table 1'[Car Model],'Table 1','Table 1'[Failure Category])
This effectively created a multidimensional table that if interrogated (I've created a Matrix and setup different data slicers just to check if the above worked) gives me the right count of failures per month, or per operators, or per car models, etc...
I now need to somehow calculate the rates by dividing "Count" by either number of hours or number of cycles (maintaining the correct operator, failure category and date).
I've tried to create relationships between the two tables through other simple 3 tables that contain dates, car models or operators data only once, in order to relate Table 1 with those 3 tables, than those 3 tables with Table 2. However Power BI gives me an error that I can't create as many relationships due to ambiguity.
I'm a bit lost. Anyone knows how I can solve this? Thank you in advance!
Table 1
| Operator | Date | Car model | Failure Category |
| A | Apr-17 | Ferrari | Brakes |
| B | May-16 | Porche | Engine |
| C | Feb-16 | GMC | Hydraulic |
| D | Jan-14 | Porche | Electric |
| E | Apr-17 | BMW | Brakes |
| A | May-16 | GMC | Hydraulic |
| A | Feb-16 | BMW | Engine |
| C | Jan-14 | GMC | Brakes |
| D | Apr-17 | Porche | Engine |
| E | May-16 | BMW | Hydraulic |
| E | Feb-16 | GMC | Engine |
| B | Jan-14 | BMW | Brakes |
| A | Apr-17 | Porche | Engine |
Table 2
| Operator | Date | Monthly Utilization (hours) | Monthly Utilization (cycles) |
| A | Apr-17 | 2500 | 120 |
| B | Apr-17 | 1324 | 764 |
| C | Apr-17 | 5637 | 761 |
| A | May-17 | 745 | 450 |
| B | May-17 | 8463 | 770.5 |
| C | May-17 | 9457 | 869.2 |
| A | Jun-17 | 4546 | 967.9 |
| B | Jun-17 | 2987 | 1066.6 |
| C | Jun-17 | 3526 | 1165.3 |
17 Replies
Anonymous,
You may refer to the following measure.
Measure = DIVIDE ( COUNTROWS ( Table1 ), SUMX ( FILTER ( Table2, Table2[Operator] = MAX ( Table1[Operator] ) && Table2[Date] = MAX ( Table1[Date] ) ), Table2[Monthly Utilization (hours)] ) )Thanks!
I've created the measure, a couple of considerations:
1- If I select a data range, I understand that the measure will return the sum of the different rates calculated for each month. I tried to replace SUMX with AVERAGEX to calculate the average instead, however the final number was the same, i.e. the sum of all the calculates rates. How can I calculate the average of the calculated rates?
Example:
- Jan rate = 0.2
- Feb rate = 0.4
Current measure gives me rate = 0.6 (if I extend the data range to Jan and Feb) . I'd like the measure to return (0.2 + 0.4)/2 = 0.3
2- If in a particular month there are no events, instead of a blank value, how can the measure return a zero? I tried with an IF statement at various levels of the measure expression you suggested, but I couldn't make it work.
For example:
- Jan: 1 event, 10 hours utilization --> Rate = 1/10 = 0.1
- Feb: 0 events, 10 hours utilization --> Rate = 0/10 = 0
I'd like to calculate the average rate as being: Average Rate = (0.1 + 0)/2 = 0.05. At the moment the measure returns 0.1 because there is no calculated value for February.
Thank you very much!
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?
