Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Help with formula returning information instead of null

How can I rewrite the highlighted formula so that it will be blank instead of 100% for the months in the future on the "Average Margin" tile?  I want it to be blank like all the rest of the tiles to the right of it.

 

Need Help
  • Sean's avatar
    Sean
    9 years ago
    Margin% =
DIVIDE (
    SUM ( AvgPricePer[Invoice Charge] ) - SUM ( AvgPricePer[Cost] ),
    SUM ( AvgPricePer[Invoice Charge] ),
    0
)

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous

     

    You could try it using IF.  So somthing along the lines of this

     

    Margin% = IF(
			(1-(sum(AvgPricePer[Cost]) / sum(AvgPricePer[Invoice Charge]))) = 1 ,
			BLANK(),
			1-(sum(AvgPricePer[Cost]) / sum(AvgPricePer[Invoice Charge]))
			)

    Also you might want to have a look into the DIVIDE function

     

    So rather that write x = y / z

    you can write x = DIVIDE(y,z)  to get better results

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      9 years ago
      Margin% =
DIVIDE (
    SUM ( AvgPricePer[Invoice Charge] ) - SUM ( AvgPricePer[Cost] ),
    SUM ( AvgPricePer[Invoice Charge] ),
    0
)