CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Jayant Patodi
Jayant Patodi

Reputation: 11

Count NA's at a customer level using measures in Power BI

I have a table with the customer name and its product code. I want to count the number of products having its name as "NA" on a customer level using only measures as I don't have access to create new columns.

Customer Name  Product Code
---------------------------
Customer 1      NA
Customer 1      NA
Customer 1      999
Customer 2      888
Customer 2      777
Customer 3      NA
Customer 3      666
Customer 3      NA
Customer 4      5

Output should be something like this:

Customer Name  Product Code
---------------------------
Customer 1      2
Customer 2      0
Customer 3      2
Customer 4      0

Upvotes: 0

Views: 397

Answers (2)

StelioK
StelioK

Reputation: 1781

You can do it in various ways, here are a couple:

The first like Marcus suggested:

NA Count :=
CALCULATE (
    COUNT ( 'Table'[Product Code] ),
    KEEPFILTERS ( FILTER ( 'Table', 'Table'[Product Code] = "NA" ) )
)

Or, Using SUMMARIZECOLUMNS, which will create a table:

NA COUNT :=
SUMMARIZECOLUMNS (
    'Table'[Customer Name],
    "NA Count", CALCULATE (
        COUNT ( 'Table'[Product Code] ),
        KEEPFILTERS ( FILTER ( 'Table', 'Table'[Product Code] = "NA" ) )
    )
)

But since you cant create calculated columns, I am assuming you wont be able to create a new table either :) Good luck and have fun!

Upvotes: 0

Marcus
Marcus

Reputation: 541

In this case, it sounds like you want to leverage the calculate function which is used modify calculation context.

The example below, the count of your Product Code, is being modified so that it only is done on rows in which the product code is NA.

NA count:= CALCULATE ( COUNT ( table[ Product Code ] ) , table[ Product Code ] = "NA" )

Calculate is a powerful function, since you can use it to both restrict or expand how the calculation works based on what filter conditions you provide.

Upvotes: 1

Related Questions