Forum Discussion

Anonymous

Not applicable

8 years ago

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 acc...

Community Support

8 years ago

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
Not applicable
8 years ago
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!