Forum Discussion

jakeryan56's avatar
jakeryan56
Advocate II
7 years ago

Count based on multiple conditions

Hi, I have a requirement that I am having trouble wrapping my mind around even though it is probably simple. 

 

I have a table called SalesData. It contains various attributes about a sale and below is a simplified example of the data.

 

 

We would like to create the following measures based on this data and then filter this to a SalesRep level. 

-Invoices with 1 item sold

-Invoices with 2 items sold

-Invoices with 3 items sold

-Invoieces with 4+ items sold

 

So just to be clear, this isn't the average (i.e. Total products/Total unique invoices created)  - this needs to be a count of the number of instances the above conditions are met.

 

Any ideas?

Need Help

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Try this measure

     

    Invoices with 1 item sold = COUNTROWS(FILTER(SUMMARIZE(VALUES(Sales_Data[Invoice]),[Invoice],"ABCD",COUNTA(Sales_Data[Description])),[ABCD]=1))

     

    Just change the filter condition to 2,3 etc. for computing other measures.

     

    Hope this helps.

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi jakeryan56,

     

    Have you solved your probelm?

     

    If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If you still need help, please share your desired output so that we could help further on it.

     

    Best Regards,

    Cherry