Count based on multiple criteria

[Anonymous]

Hello,

I am a newbie integrating Power BI at my workplace. Could you please help with a query to sum/count based on multiple criteria.

I have a data set of each customer and  Sales rep assigned to that customer and number of sales units sold to each customer. 

I want a column that has Count of sales units >=1  sold  - rolled up by each sales Rep -  though the data set has rows for each customer.  Table should be as shown below. Please see example below.

IE - JEFF has 2 customers where units sold >=1 

Sales Rep - Customer - Units Sold - Count of Sales units >1 (ALL Customers of that sales rep)

JEFF       -     ABC                    3          -     2

Dave     -       XYZ                1          -    1

JEFF     -        BNM                 7          -   2

Thanks for your help!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please create a calculated column with below formula:

Count of Sales Unit =
CALCULATE (
    DISTINCTCOUNT ( Table[Customer] ),
    FILTER ( ALLEXCEPT ( Table, Table[Sales Rep] ), Table[Units Sold] >= 1 )
)

Best regards,

Yuliana Gu