Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tnelson
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
dax
Community Support
Community Support

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.

tnelson
Frequent Visitor

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. 

dax
Community Support
Community Support

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.

tnelson
Frequent Visitor

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?

Ashish_Mathur
Super User
Super User

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/

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? 

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.