Home Dashboard Directory Help
Search

PowerPivot KPI configuration can result in divide by zero by peter_myers


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 866074
Opened: 5/2/2014 9:46:23 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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).
Details
Sign in to post a comment.
Sign in to post a workaround.