Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Countif problem

Hi everyone ,   I have a table indicating number of sold units per product for each customer key. What I want to do is calculating countif formula like excel by counting row disregarding blanks per...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Unfortunately, you post could not help me. For best understanding , I am repeating my example's data.

 

Ismail Kazan is sales rep , numbers below it are his customers and total right side include number of products sold. So at the end of column I want to bring "4" for last row for my example by using countif formula. Because customer indicated "4022_337" got only 4 different product rather than considering total.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi everyone ,

 

For everyone who will be dealing this topic , you can also consider this topic for further informations.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ，

    I created a sample pbix file(see attachment),  please check whether that is what you want.

    1. Create a measure as below to get the number of unit sold

     

    Sum of amount = SUM('Sales'[amount])

     

    2. Create another measure as below to get countif 

     

    Countif = 
IF (
    ISINSCOPE ( Sales[type] ),
    SUMX (
        GROUPBY ( 'Sales', 'Sales'[sales rep], Sales[product], 'Sales'[type] ),
        [Sum of amount]
    ),
    COUNTX (
        GROUPBY ( 'Sales', 'Sales'[sales rep], Sales[product], 'Sales'[type] ),
        [Sum of amount]
    )
)

     

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous 

       

      First of all , thanks for huge help to get right result so first phase of my question has been done as you see below.

      But lastly , I want to count those result which resulted from countif formula like how many customers(which are below ismail kazan) ">=2". For this example , we should get "3" for Ismail Kazan because 3 customers have been sold at least 2 different product. Can you help me for this ?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        Sorry for delay. I tried to implement your new requirements, but failed. Row total only shows the summary value of each row, but not the number of eligible ...

        Best Regards