Calculate rate from two related tables

[Anonymous]

Hi there! I'm new to Power BI and have been trying for the last few days to create some measures functions but no luck!

In summary, I have two tables loaded in PBI Desktop file that look like:

Table 1

Customer	Date	Event type
A	3-Apr-17	AA
A	10-Jun-17	BB
A	1-Jan-17	GG
B	30-Nov-16	AA
C	17-Oct-15	GG
A	3-Apr-17	TT
A	10-Jun-17	HH
A	1-Jan-17	BB
C	30-Nov-16	CC
A	17-Oct-15	RR
C	3-Apr-17	SS
B	10-Jun-17	DD
C	1-Jan-17	CC

The second table looks like:

Table 2

Customer	Month	Monthly Utilization
A	1-Jan-17	15
A	1-Feb-17	10
A	1-Mar-17	13
B	1-Jan-17	17
B	1-Feb-17	20
B	1-Mar-17	80
C	1-Jan-17	40
C	1-Feb-17	10
C	1-Mar-17	12
D	1-Jan-17	14
D	1-Feb-17	34
D	1-Mar-17	54

I want to create a visual showing the monthly rate of different type of events (in this case AA, BB, GG, etc...) for the different customers defined simply as:

Event Rate of one event type for a particular customer = Number of events in a particular month of that particular operator / Monthly Utilization of that particular customer.

So far I've just created a table in PBI with the "DISTINCT" function that simply created a column with all the operator names. I've created 2 relationships between this column and the operator columns of Table 1 & 2.

Could you please help me in creating measures to calculate those rates? I've tried with the COUNTA function to count the different number of events for each customer, however that only calculates the total number of events for each customer. It doesn't calculate the totals for every month. Also, I'd like the functions to be rolling, i.e. it should keep calculating the last 4 months of rates from "today".

Thanks a lot!

[v-sihou-msft]

@Anonymous

I'm still not quite clear about your requirement. 

Event Rate of one event type for a particular customer = Number of events in a particular month of that particular operator / Monthly Utilization of that particular customer

Is "Number of events in a particular month of that particular operator" group on event type and month?

I suggest you add a "first day of month" column in Table 1.

Month = Table1[Date]-DAY(Table1[Date])+1

Then you can create a measure to calculate the count of events group on Month and Event Type.

Measure = CALCULATE(COUNTROWS(),ALLEXCEPT(Table1,Table1[Customer],Table1[Event type],Table1[Month]))

Regards,

[Anonymous]

Hello,

 I've created and 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!

[Anonymous]

Thank you very much Simon.

I was able to create a measure like you said called "Events". What I need to do now is calculate the ratio between "Events" and the number of hours that the asset was used in a particular month. The number of hours is in Table 2 that looks like:

Date            Operator      Asset             Utilization (hours)

04-2017            A               X                              10

04-2017            B               Y                              20

04-2017            B               Z                              30

I have created three relationships between Date, Operator and Asset of Table 1 & Table 2. How can I create this other measure to calculate the ratio between "Events" and utilization?

For example:

Operator B in April 2017 had 2 events using the asset Y (this is calculated by the measure "Events").  I would like now to calculate the rate of events for the asset Y in April 2017:  Rate = 2 events / 20 hours

Thanks a lot.

Regards