Unexpected Infinity

[Anonymous]

Hi,

I have a dataset similar to the below.

Date	Job ID	Norm Hours	Total Hours
01/01/2021	1	10	20
01/01/2021	2	10	20

I am trying to calculate 'Contract Factor'. I have created the below DAX.

Contract Factor =SUM('Detailed Data Set'[Total Hours]) / SUM('Detailed Data Set'[Norm Hours])

When this measure is entered into a table visual I get the below response.

Date	Job ID	Norm Hours	Total Hours	Contract Factor
01/01/2021	1	10	20	2
01/01/2021	2	10	20	infinity

Infity is not expected.

I have tried creating a card visual, entering the Contract Factor measure and filtered on job '2'. The card displays the expected result '2' (total hours [20] /  norm hours [10]). 

I have also tried creating the Contract Factor calculation as a calculated column. When put into a table visual this displays infinity for all Contract Factors.

Does anyone know why I am getting unexpected 'infinity' results?

[Anonymous · Accepted solution]

Hi @Anonymous , 

Use divide to avoid divide by 0. The reason why infinity is shown is that there are some values that are 0. 0 divided by a number is infinity. You can use the divide function to avoid infinity because the function can get 0 when it encounters a value of 0. Or you can make a conditional judgment, for example a/b, if a is 0 then get 0, otherwise a/b.

I cannot reproduce your issue. 

Please try to create a measure.

IFERROR(
   SUM('Detailed Data Set'[Total Hours]) / SUM('Detailed Data Set'[Norm Hours]),
    BLANK()
    )

If I have misunderstood your meaning, please provide some sample data and desired output.

I have alse found a similar post, please refer to it to see if it helps you.

Infinity error 

• If infinity error then use divide

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

HI @Anonymous ,

Please try this:-

Contract Factor =divide(SUM('Detailed Data Set'[Total Hours]),SUM('Detailed Data Set'[Norm Hours]),0)

Thanks,

Samarth

[Anonymous]

Thanks for your quick response.

This has replaced all previous 'infinity' figures with '0' where they should still be returning a value higher than '0'.

Using the bar graph visual I have placed column 'Job ID' on the X axis of a graph visual and 'Contract Factor' DAX on the Y axis.

This returns a result with all Job ID's displaying their expected Contract Factor. Contract Factors that are displaying as 'Infinity' or '0' in a table visual now show as the expected value in a bar graph. 

I don't understand why the table visual is showing different results for the same data.

[Samarth_18]

@Anonymous Try this:-

Contract Factor =divide(SUM('Detailed Data Set'[Total Hours]),SUM('Detailed Data Set'[Norm Hours]))

[Anonymous]

Unfortunately same result. All previous 'infinity' Contract Factors are now displaying as '0'

[Samarth_18]

@Anonymous Set the value to two decimal places from the ribbon.

[Anonymous]

The values are set to two decimal places. 

For example,

Job ID 10 is showing 4.73 on the bar graph

Job ID 10 is showing 0.00 on the table visual

Job ID 10 is showing 4.73 on the card visual  

[Samarth_18]

@Anonymous Is it possible for you to share PBIX file after removing sensitive data?

[Anonymous]

Unfortunately this is not possible.

I have just tried filtering the table on a specific Job ID and adding a total column. The expected Contract Factor is 9.24 and can be seen in the total but not in the value box.