Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Divide 2 columns based on a common value

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's avatar
    tamerj1
    4 years ago

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

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

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