Divide 2 columns based on a common value

[Anonymous]

I want to perform the following division from columns in two separate tables:

Failure Ratio=

DIVIDE(

    COUNTA('Failure Table'[Distinct Install to First Failure]),

    COUNTA('All Devices By Region'[Install Year])

)

But only divide where 'All Devices By Region'[Install Year]=YEAR('Failure Table'[Install Day].

How can I modify the measure above to achieve this?

[tamerj1]

Hi @Anonymous 
In which table are creating this column? What is the relationship between the two tables? Or are you trying to create a measure? If so how does your report look like?

[Anonymous]

Hi @tamerj1 ,

I am trying to create this as a measure in the 'Failure Table' table. The two tables are not linked. Currently I have a graph with value of Failure Ratio and axis of Year('Failure Table'[Install Day]). However, currently the data is showing the Count of Distinct Install to First Failure for a given year divided by the total number devices installed, not number of devices installed that year.

[tamerj1 · Accepted solution]

@Anonymous 
Please try

Failure Ratio =
VAR CurrentYear =
    SELECTEDVALUE ( 'All Devices By Region'[Install Year] )
RETURN
    DIVIDE (
        CALCULATE (
            COUNTA ( 'Failure Table'[Distinct Install to First Failure] ),
            YEAR ( 'Failure Table'[Install Day] ) = CurrentYear
        ),
        COUNTA ( 'All Devices By Region'[Install Year] )
    )

[Anonymous]

That worked, thank you!