Countif problem

[Anonymous]

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 each sales rep level. For example in below , you see 5 number of unit sold in last row but I need to make a formula determines non-blanks cell in each row , for this example it should be 4 rather than 5. Lastly , I will re-calculate those results per each sales rep by using countif formula again.(ex.ismail kazan). Is there anyone who can help me ?

[Anonymous]

Hi everyone ,

Could you please help me regarding above issue ? Rather than posting general countif problem solving , I would definitely need a custom solution for it.

[Anonymous]

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]

Hi again everyone ,

Could you please help me regarding this issue ? Rather than using usual reply , I only need to have how to count the calculated column which is solved below(countif) by @Anonymous .

Regards

Burak

@Greg_Deckler 

Regards

Burak

[Anonymous]

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]

Hi everyone ,

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

[Anonymous]

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]

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]

Hi everyone ,

Could you please help me regarding this subject ?

Thanks

[Anonymous]

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

[Greg_Deckler]

@Anonymous Excel to DAX translation is here: Excel to DAX Translation - Microsoft Power BI Community

COUNTIF is 

COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...))