cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How can I add a function similar to COUNTIF in Power BI?

I have a unique problem that I am trying to solve.  I am working on a report that counts stores with different types of beverages. I am trying to get a distinct count of stores that are selling 4 or more Powerade flavors and two or more Coca-Cola flavors while maintaining a count of stores that are purchashing other products (Sprite, Dr. Pepper, etc.).

My data table is BEVSALES and the data looks like:

CustomerNo     Brand     Flavor
     43         PWD    Fruit Punch 
     37      Coca-Cola    Vanilla
     43         PWD     Mixed Bry
     37      Coca-Cola    Cherry
     44        Sprite   Tropical Mix
     43         PWD     Strawberry
     43         PWD       Grape
     44      Coca-Cola    Cherry  
     17      Dr. Pepper   Cherry

I am trying to make the data give me a distinct count of customers with filters that have PWD>=4 and Coca-Cola>=2, while keeping the customer count of Dr. Pepper and Sprite at 1 each. (1 customer purchasing PWD, 1 customer Purchasing Coca-Cola, etc.)

 

The best measure that I have been able to find is

= SUMX(BEVSALES, 1*(FIND("PWD",BEVSALES[Brand],,0)))

but I don't know how to put it together so the formula counts the stores that have more than 4 PWD and 2 Coca-Cola flavors. I am thinking it is something to the effect of

CALCULATE(DISTINCTCOUNT(BEVSALES[CustomerNo]), 
SUM(BEVSALES[Flavor] = "Fruit Punch"||BEVSALES[Flavor] = "Mixed Bry"||BEVSALES[Flavor] = "Strawberry"||
BEVSALES[Flavor] = "Grape")>=4)

However, that formula returns an error. 

 

Does anyone have any ideas that would work better than what I have been able to come up with?

 

Thanks in advance!

7 REPLIES 7
Highlighted
Super User V
Super User V

Re: How can I add a function similar to COUNTIF in Power BI?

Hi,

Try this measure

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[CustomerNo]),Data[CustomerNo],"ABCD",CALCULATE(COUNTROWS(Data),Data[Brand]="PWD"),"EFGH",CALCULATE(COUNTROWS(Data),Data[Brand]="Coca-Cola")),[ABCD]>=4&&[EFGH]>=2))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: How can I add a function similar to COUNTIF in Power BI?

Thanks for the quick reply. I tried the formula in and it came back blank. Any idea why it would come back blank?

 

Edit: It seems like the formula is coming back blank because I need exact wording. Is there a way to do a variable in case the data has a space included somewhere? 

Highlighted
Super User V
Super User V

Re: How can I add a function similar to COUNTIF in Power BI?

Hi,

Use the TRIM function in a calculated column to get rid of the extra spaces.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Re: How can I add a function similar to COUNTIF in Power BI?

Hi tnelson, 

I am not sure your detailed requirement, so if possible, could you please inform me more detailed information (your expecting output)?

In addition, you could try below measure to get distinct count

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( test[Flavor] ),
    ALLEXCEPT ( test, test[customerno], test[brand] )
)

267.PNG

 You said you willget blank when use below measure

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[CustomerNo]),Data[CustomerNo],"ABCD",CALCULATE(COUNTROWS(Data),Data[Brand]="PWD"),"EFGH",CALCULATE(COUNTROWS(Data),Data[Brand]="Coca-Cola")),[ABCD]>=4&&[EFGH]>=2))

You could try to change this to below(use "or" in measure)

=COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[CustomerNo]),Data[CustomerNo],"ABCD",CALCULATE(COUNTROWS(Data),Data[Brand]="PWD"),"EFGH",CALCULATE(COUNTROWS(Data),Data[Brand]="Coca-Cola")),[ABCD]>=4||[EFGH]>=2))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: How can I add a function similar to COUNTIF in Power BI?

Here is what I am looking for:

 

In the table below it shows customer #43 sells 4 flavors of PWD. 

CustomerNo     Brand     Flavor
     43         PWD    Fruit Punch 
     37      Coca-Cola    Vanilla
     43         PWD     Mixed Bry
     37      Coca-Cola    Cherry
     44        Sprite   Tropical Mix
     43         PWD     Strawberry
     43         PWD       Grape
     44      Coca-Cola    Cherry  
     17      Dr. Pepper   Cherry

I want customer 43 to be counted as 1 outlet selling 4 flavors of PWD. Since the outlet would probably be selling other brands and flavors, I would like for it to also include the other brands and flavors.

 

When I do this in Excel, I can do a function:

 

=COUNTIFS('POWERADE 32z Detail'!A:A,Summary!A5,'POWERADE 32z Detail'!AC:AC,">=4")

Where Excel counts the outlet only if it is selling 4 or more flavors. I cannot figure out how to do a function similar to this in Power BI. 

 

In short, I am looking for a distinct count of customers that are sellling 4 or more distinct flavors of PWD. I don't want Power BI to count the outlet if it doesn't have 4 or more PWD flavors.

 

Hopefully this explanation makes more sense. 

Highlighted
Community Support
Community Support

Re: How can I add a function similar to COUNTIF in Power BI?

Hi tnelson, 

Did you want to only show 43 in  table? If so, you could set filter on measure 

314.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

Re: How can I add a function similar to COUNTIF in Power BI?

Not quite.

 

I am trying to track progess with certain criteria. So if an outlet is selling 2 Coca-Cola, 1 Sprite, and 4 PWD flavors then I need a function that can count the outlet based on that criteria. I am looking for a function that tells Power BI to count an outlet under a Coca-Cola column (ONLY if the outlet is selling 2 Coca-Cola flavors) and then count an outlet under a PWD column (ONLY if the outlet is selling 4 PWD flavors). 

 

Here is a shortened explanation of what I am looking for:

 

The table below has a count of flavors being sold by brand and how many flavors each customer is carrying.

CustomerNo     Brand     Number of Flavors
     37         PWD              6
     37      Coca-Cola           3
     43         PWD              2
     43      Coca-Cola           1
     43        Sprite            1
     44         PWD              5
     45         PWD              7
     47      Coca-Cola           2
     47        Sprite            1

I am looking to find a way that will count the number of outlets selling each brand by number of flavors. The table would look like this:

 

                                        PWD (4 or more)         Coca-Cola (2 or more)       Sprite
Outlets Selling                              3                          2                      2

Where customers 37, 44, and 45 are selling 4+ PWD; customers 37 and 47 are selling 2+ Coca-Cola; and customers 43 and 47 are selling Sprite. 

 

The function would need to count every outlet selling 4+ PWD, 2+ Coca-Cola, and 1+ Sprite. Does that make a little more sense?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors