PowerPivot KPI configuration can result in divide by zero - by peter_myers

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 866074 Comments
Status Resolved Workarounds
Type Bug Repros 0
Opened 5/2/2014 9:46:23 PM
Access Restriction Public


If defining a KPI in Power Pivot (all versions, and probably in the Tabular Project also), by using target calculated field (measure) it produces the following script logic:

CREATE MEASURE 'Quota'[Sales Performance]=[Sales];
CREATE MEASURE 'Quota'[_Sales Performance Goal] = 'Quota'[Quota]; 
CREATE MEASURE 'Quota'[_Sales Performance Status] = if(ISBLANK('Quota'[Sales Performance]/'Quota'[_Sales Performance Goal]),BLANK(),
        If('Quota'[Sales Performance]/'Quota'[_Sales Performance Goal]<0.9,-1,
	    If('Quota'[Sales Performance]/'Quota'[_Sales Performance Goal]<1,0,1)

In this example, if 'Quota'[_Sales Performance Goal] is blank or zero, this formula will produce an error. The correct expression should involve the use of the DAX DIVIDE function for all three ratios. This would eliminate any such errors.

I've noticed that PivotTables will tolerate such errors arising, but Power View will produce  non-descript error message when it encounters a division by zero (or blank).
Sign in to post a comment.