Count Distinct Values

[Greig]

I have created a Power Pivot report from a data cube. It looks like:

Column A Column B Column C
Affirmed
Affirmed
Varied Affirmed Affirmed
Varied Affirmed Cancelled

I want to count how many times the word Affirmed appears in the report. I have tried various formulas without luck. I am using Excel 2010. Thanks Greig

 

[shawnhet]

=COUNTIF(A:C,"Affirmed")

should work

Cheers,

 

[Greig]

Thank you, but COUNTIF does not seem to be a valid formula for reports created from PowerPivot.

Greig

=COUNTIF(A:C,"Affirmed")

should work

Cheers,

 

[luirib]

Have you tried with DISTINCTCOUNT where you set a filter of the word "Affirmed"?

 

[Jacob Barnett]

Hey, few ways you could approach this:

- the 'simplest' is probably to create a calculated column in your table that uses the SEARCH() function to check for affirmed in each column and add 1s where it is the case to give you a count e.g.:

=
IFERROR (
    IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column A] ) ), 1, 0 ),
    0
)
    + IFERROR (
        IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column B] ) ), 1, 0 ),
        0
    )
    + IFERROR (
        IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column C] ) ), 1, 0 ),
        0
    )

This isn't pretty and perhaps there is a more elegant way but it works! The way SEARCH() works in DAX is stupid as if it doesn't find the string it produces an error regardless of the ISNUMBER() so has to be wrapped in an IFERROR().

- the 'better' way is to do it in a measure. This won't be particularly clean as PowerPivot is 'column' based so you will need to do the calc for each column. A measure would look like this:

=
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Column A]
        = "Affirmed"
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Column B]
            = "Affirmed"
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Column C]
            = "Affirmed"
    )

Again this isn't going to win prizes for elegance but gets it done. Note this counts how many times the word appears not how many rows it appears in!

 

[Greig]

Many thanks Jacob. Help is much appreciated. Greig

Hey, few ways you could approach this:

- the 'simplest' is probably to create a calculated column in your table that uses the SEARCH() function to check for affirmed in each column and add 1s where it is the case to give you a count e.g.:

=
IFERROR (
    IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column A] ) ), 1, 0 ),
    0
)
    + IFERROR (
        IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column B] ) ), 1, 0 ),
        0
    )
    + IFERROR (
        IF ( ISNUMBER ( SEARCH ( "Affirmed", [Column C] ) ), 1, 0 ),
        0
    )

This isn't pretty and perhaps there is a more elegant way but it works! The way SEARCH() works in DAX is stupid as if it doesn't find the string it produces an error regardless of the ISNUMBER() so has to be wrapped in an IFERROR().

- the 'better' way is to do it in a measure. This won't be particularly clean as PowerPivot is 'column' based so you will need to do the calc for each column. A measure would look like this:

=
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Column A]
        = "Affirmed"
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Column B]
            = "Affirmed"
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        Table1[Column C]
            = "Affirmed"
    )

Again this isn't going to win prizes for elegance but gets it done. Note this counts how many times the word appears not how many rows it appears in!

 

[jlukens]

Can someone please tell me how to Count only the distinct values in column a? Example:

Name of Column A: Contact_ID

Values in Column A:
123456789
321456987
321456987
321456987
951236958
8896587
556987
556987
23258877
362569878
362569878

I only want excel to count that there are 7 - I want them to ignore the duplicates. this report has to show the dups, but only count the "people".

Please help

 

[ImkeF]

Have a look here: Count Unique Values in Excel - Easy Excel Tutorial

hth, Imke