Search code examples
powerbinumber-formatting

Remove Zeros Before Decimal- PowerBI

Numbers under 1 are currently being represented with a leading zero before the decimal point (example: 0.50). Because I'm working with baseball statistics (which almost never have the zero before the decimal) I would like to remove that. I want to keep the number before the decimal if its greater than 1 though. How would I do that?

For instance if I'm working with this measure. Is there something I can add to that?

AVG = SUM(Batter[H])/sum(Batter[AB])

Thanks. I appreciate the help.

Here is some sample data

Name            AB  H   
Gleyber Torres  546 152 
Brett Gardner   491 123 
Aaron Judge     378 103 
Adam Ottavino   0   0
Aroldis Chapman 0   0
Solution

  • The NAN error is occurring because you are dividing by 0. You should add an IF condition to avoid that:

    AVG = IF(sum(Batter[AB])=0,BLANK(),SUM(Batter[H])/sum(Batter[AB]))

    To tackle the formatting issue you can use the FORMAT function as mentioned by Andrey:

    AVG = IF(sum(Batter[AB])=0,BLANK(),FORMAT(SUM(Batter[H])/sum(Batter[AB]),"###.0#"))

    Hope this helps.