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 Re...
  • 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] )
    )
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] )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        That worked, thank you!