cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IMK Regular Visitor
Regular Visitor

Need help with null cells

I am calculating NPS with Power BI and created a new column for this. I have Grades column that holds grades given by customer and I ma creating new column which looks at the grade in Grade column and changes the grade to either Detractors, Passives or Promoters.

 

My problem is that some cells in Grades column may be empty. Now my syntax counts these empty cells as Detractors but it should count it as "Empty". How can I get the syntax to count empty cells as empty cells??

 

Column = SWITCH(TRUE();'Table1'[Grades]>=0 && 'Table1'[Grades]<=6;"Detractors";'Table1'[Grades]=7 ||'Table1'[Grades]=8; "Passives";'Table1'[Grades]=9 || 'Table1'[Grades]=10;"Promoters"; "Empty")

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Need help with null cells

@IMK

This is happening because the empty values are treated as zeros and falling into the first condition

There's couple ways you can handle this depending on whether 0 is actually a legitimate value or not.

(meaning could you really have a zero as a grade)

So if zero can't be really a value all you have to change is the comparison from >= 0 to just > 0

Column 2 =
SWITCH (
    TRUE ();
    'Table1'[Grades] > 0
        && 'Table1'[Grades] <= 6; "Detractors";
    'Table1'[Grades] = 7
        || 'Table1'[Grades] = 8; "Passives";
    'Table1'[Grades] = 9
        || 'Table1'[Grades] = 10; "Promoters";
    "Empty"
)

Now if zero can in fact be a grade use this instead which first checks if the value is blank

Column 3 =
IF (
    ISBLANK ( 'Table1'[Grades] );
    "Empty";
    SWITCH (
        TRUE ();
        'Table1'[Grades] >= 0
            && 'Table1'[Grades] <= 6; "Detractors";
        'Table1'[Grades] = 7
            || 'Table1'[Grades] = 8; "Passives";
        'Table1'[Grades] = 9
            || 'Table1'[Grades] = 10; "Promoters"
    )
)

Hope this helps! Smiley Happy

Super User
Super User

Re: Need help with null cells

When you say SUM I suspect you mean COUNT each category and the overall total excluding the "empty" values

So to calculate each category individually...

Detractors =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Detractors" )
)

Passives =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Passives" )
)

Promoters =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Promoters" )
)

Then to calculate the overall total - couple ways depending on which column you decide to FILTER

Total (NonBlank) =
CALCULATE (
    COUNTA ( Table1[Grades] );
    FILTER ( Table1; Table1[Grades] <> BLANK () || Table1[Grades] = 0 )
)

Total (NonBlank) 2 =
CALCULATE (
    COUNTA ( Table1[Grades] );
    FILTER ( Table1; Table1[Column 3] <> "Empty" )
)

If you FILTER the [Grades] column as in the first formula you have to exclude blanks but include zeros

If you FILTER [Column 3] instead all you have to exclude is "Empty" since we already took care of this in the [Column 3] formula

Hope this helps! Smiley Happy

5 REPLIES 5
Super User
Super User

Re: Need help with null cells

@IMK

This is happening because the empty values are treated as zeros and falling into the first condition

There's couple ways you can handle this depending on whether 0 is actually a legitimate value or not.

(meaning could you really have a zero as a grade)

So if zero can't be really a value all you have to change is the comparison from >= 0 to just > 0

Column 2 =
SWITCH (
    TRUE ();
    'Table1'[Grades] > 0
        && 'Table1'[Grades] <= 6; "Detractors";
    'Table1'[Grades] = 7
        || 'Table1'[Grades] = 8; "Passives";
    'Table1'[Grades] = 9
        || 'Table1'[Grades] = 10; "Promoters";
    "Empty"
)

Now if zero can in fact be a grade use this instead which first checks if the value is blank

Column 3 =
IF (
    ISBLANK ( 'Table1'[Grades] );
    "Empty";
    SWITCH (
        TRUE ();
        'Table1'[Grades] >= 0
            && 'Table1'[Grades] <= 6; "Detractors";
        'Table1'[Grades] = 7
            || 'Table1'[Grades] = 8; "Passives";
        'Table1'[Grades] = 9
            || 'Table1'[Grades] = 10; "Promoters"
    )
)

Hope this helps! Smiley Happy

IMK Regular Visitor
Regular Visitor

Re: Need help with null cells

Hi

 

Thanks for you'r answer!! Zero can indeed be a grade so I will use the second syntax, which works great! Thanks!

 

Just one thing more.. I would need to be able to calculate the sum of Detractors, Passives and Promoters but cells marked as Empty should not be calculated to the sum. 

 

How can I do this?

 

After I get this sorted, I am ready to publish the ready syntax to calculate NPS score using Power BI.

Super User
Super User

Re: Need help with null cells

When you say SUM I suspect you mean COUNT each category and the overall total excluding the "empty" values

So to calculate each category individually...

Detractors =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Detractors" )
)

Passives =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Passives" )
)

Promoters =
CALCULATE (
    COUNTA ( Table1[Column 3] );
    FILTER ( 'Table1'; 'Table1'[Column 3] = "Promoters" )
)

Then to calculate the overall total - couple ways depending on which column you decide to FILTER

Total (NonBlank) =
CALCULATE (
    COUNTA ( Table1[Grades] );
    FILTER ( Table1; Table1[Grades] <> BLANK () || Table1[Grades] = 0 )
)

Total (NonBlank) 2 =
CALCULATE (
    COUNTA ( Table1[Grades] );
    FILTER ( Table1; Table1[Column 3] <> "Empty" )
)

If you FILTER the [Grades] column as in the first formula you have to exclude blanks but include zeros

If you FILTER [Column 3] instead all you have to exclude is "Empty" since we already took care of this in the [Column 3] formula

Hope this helps! Smiley Happy

IMK Regular Visitor
Regular Visitor

Re: Need help with null cells

Great, thanks!

 

So I believe that the following works:

 

Calculating NPS Net Promoter Score using Power BI:

 

When the Column with NPS grades are named "Grades", following works:

 

Modeling -tab -> New Column

 

Column =
IF (
    ISBLANK ( 'Table1'[Grades] );
    "Tyhja";
    SWITCH (
        TRUE ();
        'Table1'[Grades] >= 0
            && 'Table1'[Grades] <= 6; "Detractors";
        'Table1'[Grades] = 7
            || 'Table1'[Grades] = 8; "Passives";
        'Table1'[Grades] = 9
            || 'Table1'[Grades] = 10; "Promoters"
    )
)

Modeling -tab -> New Measure

 

NPS = var NumOfRespon= CALCULATE (
    COUNTA ( Table1[Grades] );
    FILTER ( Table1; Table1[Grades] <> BLANK () || Table1[Grades] = 0 )
)
return 
(CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Promoters"))/NumOfRespon-CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Detractors"))/NumOfRespon) *100

This syntax will ignore all empty cells (rows) in the grades column and do not include empty's in the NumOfRespon which is used as divider for the final NPS score.

IMK Regular Visitor
Regular Visitor

Re: Need help with null cells

Thanks a bunch again @Sean!! You have been very helpfull!!