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 ,

You can create a calculated column as below:

Column =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Item] = EARLIER ( 'Table'[Item] )
            && 'Table'[Subitem] = EARLIER ( 'Table'[Subitem] )
    )
)

In addition, you can refer the content in the following links to get it.

COUNTIF in Power BI

COUNTIFS in DAX for Power Pivot or Power BI?

Power BI COUNTIF

Best Regards

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 ?