Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count based on multiple criteria

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!

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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