Rate between tables

[Anonymous]

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

[Ashish_Mathur]

Hi @Anonymous,

I am not sure of your expected.  See my file here.

[Anonymous]

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!

[Ashish_Mathur]

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?

[Anonymous]

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!

[Ashish_Mathur]

Hi,

Please share the download link of the Excel file where you have all Tables.

[Anonymous]

@Ashish_Mathur

Here is the Excel file. Thanks!

[Ashish_Mathur]

Hi,

I think the missing piece of information here is the Failure type column in the Utilisation Data table.  Unless we have that column in that table, we would not know which rows have to be summed up from the utilisation data table.

[Anonymous]

@Ashish_Mathur
Yes I agree. That's why I tried to insert columns in the Utilization data table to count the number of events from the event data, however PowerBi wouldn't let me insert more than one calculated column in the Utilization data table (circular dependency error). I would need three columns for counting the three different kinds of failures for each month.
After searching in other blogs, I found this comment for a similar issue:

"Power BI does not allow to have two calculated columns that contain measures that are also based on that table. In order to understand why, you'd need a better understanding of what's going on under the hood. In order to get around it, you should try turning these into measures".

So it looks like I would need to create measures to count the number of events. Any idea how?

Thanks!

[Ashish_Mathur]

Hi,

Without that coolumn, i dno't think we can solve this problem.  We just need that one column and  think we will be sorted.  Also, i don't think that will be a caculated column - that will be an input which has to come straight from the user or from your system where you imported this data from.

[Anonymous]

@Ashish_Mathur
Hi! Unfortunately the values in that column don't come form the input fle, and cannot be entered by the user as we're talking about hundreds of data points.
Those values (total events for each month) needs to be calculated from the event data. The event data table gives you one line for each event. We need to create a measure that calculates the total number of events for each month, each customer, each type of asset and each failure mode.
Thank you.

[Ashish_Mathur]

Hi,

Calculating the number of events is not a problem because that is a simple count rows operation from Table1.  The problem is calculating the ratio because the denominator has to come from Table2.  So unless we have type of Failure in Table2, we will not be able to compute the denominator.

[Anonymous]

@Ashish_Mathur

Hi Ashish,

  Just to clarify, what do you mean by having types of failures in Table 2? What is it that you want to add on Table 2 as a column? Bear in mind there are 3 types of failures which can happen in the same month, and multiple times per month.

Thank you.

[Ashish_Mathur]

Hi.

On Table 2, there should be a column for type of failures (the same one that you have in Table 1).

[Anonymous]

I have been trying to do that for one type of failure to begin with. Unfortunately as I said before I can only create one calculated column to count the sum of one type of failure every month in Table 2. If I then try to create a second calculated column to count the second type of failure, or to simply calculate the rate of the first column divided by the total monthly hours, there is an error in PBI about "circular dependency detected". I can't create more than one calculated column apparently.

[Anonymous]

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.

[v-chuncz-msft]

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

[Anonymous]

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!