Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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] ) )
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.
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.
Hi tnelson,
Did you want to only show 43 in table? If so, you could set filter on measure
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.
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?
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.
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.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |