topic Re: Divide 2 columns based on a common value in DAX Commands and Tips

Divide 2 columns based on a common value

Anonymous — Mon, 25 Jul 2022 13:39:49 GMT

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?

Re: Divide 2 columns based on a common value

tamerj1 — Mon, 25 Jul 2022 18:26:58 GMT

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?

Re: Divide 2 columns based on a common value

Anonymous — Mon, 25 Jul 2022 18:37:51 GMT

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.

Re: Divide 2 columns based on a common value

tamerj1 — Mon, 25 Jul 2022 19:06:21 GMT

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

Re: Divide 2 columns based on a common value

Anonymous — Tue, 26 Jul 2022 12:33:16 GMT

That worked, thank you!