Forum Discussion
Handling infinity value
Hello,
I am currently using a measure to calculate Sales Rate. The calculation is as follows FactTable[X]/FactTable[Y]. The X and Y come from different Fact tables. If Y is not present for the corresponding date, I am getting a value of infinity. It would be good to have nothing show up then to have infinity value. How do I achieve this?
Thanks in advance
Hi,
Thanks for the reply.
I got a solution online before I came across your post. Refer below for the solution.
IFERROR( [Mesaure 1]/[Mesaure 2], BLANK() )where Mesaure 1 is Sum(FactTable[x])and Mesaure 2 is Sum(FactTable[y])
Found it in this blog- https://sqldusty.com/
8 Replies
Found this alternative without getting 'Cannot load Visual Error', DIVIDE function
DIVIDE(numerator,denominator,0)
Reference: https://www.dutchdatadude.com/power-bi-pro-tip-divide-function/
This is a better solution than the one that is posted as the solution. Thank you!
This is not ideal. You are replacing infinity with 0, which it should not be.
DIVIDE(numerator,denominator) is the correct option, yielding blank for the undefined fraction.
If FactTable[Y] is zero just display blank for example:
Measure = IF(FactTable[Y]=0,BLANK(),FactTable[X]/FactTable[Y])
Hi,
Thanks for the reply.
I got a solution online before I came across your post. Refer below for the solution.
IFERROR( [Mesaure 1]/[Mesaure 2], BLANK() )where Mesaure 1 is Sum(FactTable[x])and Mesaure 2 is Sum(FactTable[y])
Found it in this blog- https://sqldusty.com/
Radacad has a very thorough blog about this - best practice etc.
https://radacad.com/make-your-numeric-division-faultless-in-power-query
