Divide sum of one column by the count of another

[android1]

Hi,

I have a column LoS which is in number format with a sum total. The other column, ID, is a Count of text values.

Is it possible to divide LoS by ID?

Gerry

[malagari · Accepted solution]

Sure - you can do this as a DAX measurement.  Unless I'm misunderstanding your post, it should be simple as:

Measure = LoS / ID

And if you're worried about the possibility that 'ID' will be zero, you want to account for the divide-by-zero case with:

Measure = DIVIDE(LoS, ID, 0)

However, if you need the aggregations (sum of LoS, count of ID) done in the measurement:

Measure = DIVIDE( SUM(LoS), COUNT(ID), 0 )

 And alternately, if the ID is text values, you can use COUNTROWS(ID) instead.

[malagari · Accepted solution]

Sure - you can do this as a DAX measurement.  Unless I'm misunderstanding your post, it should be simple as:

Measure = LoS / ID

And if you're worried about the possibility that 'ID' will be zero, you want to account for the divide-by-zero case with:

Measure = DIVIDE(LoS, ID, 0)

However, if you need the aggregations (sum of LoS, count of ID) done in the measurement:

Measure = DIVIDE( SUM(LoS), COUNT(ID), 0 )

 And alternately, if the ID is text values, you can use COUNTROWS(ID) instead.

[android1]

Thanks alot. What I was doing wrong was I had the result to display in a KPI visual rather than a Card so I thought LoS / ID wasn't correct.

Thanks again.

[Phil_Seamark · Accepted solution]

So make sure you click the "New Measure" button on the modelling tab.

or right click the table name and choose "New Measure" and add the above code by @malagari

Measure = DIVIDE( SUM(LoS), COUNT(ID), 0 )