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