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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pbi_girl_21
New Member

Counting Products based on Indicator

Hi Everyone,

 

I have a complex calcuation I can't seem to figure out!

 

I have a table of data with grocery store names and products. The products are fruits, veggies, meat etc... but I only care about fruit. Here's an example:

StoreProductFruit Indicator
Store ABeans 
Store BStrawberry1
Store CLettuce 
Store AStrawberry1
Store BBanana1
Store BStrawberry1

 

I am trying to identify which stores have no fruit, one fruit, or more than one. This also means I only want a unique count of each fruit type. The expect output is this:

StoreUnique Fruit Count
Store A1
Store B2
Store C

0


I have the below calculation which gives me my expected output:

 

Unique Fruit Count = CountRows(Filter ( Summmarize ( 'StoreData', 'StoreData'[Store], 'StoreData'[Product], 'StoreData'[Fruit Indicator]), 'StoreData'[Fruit Indicator] = 1 )) +0

 

However, this measure is giving me trouble when trying to work with it. I would like to now graph how many times fruits are part of a group of unique fruits per store. For example, in my above example I would graph Store B with strawberreis and banana on the x axis and each having a y axis value of 1 and 1 respectively. But Store A would not be included because it's unique fruit is count 1, Store C would not be included because it has no unique Fruit.

 

Does anybody have any advice? I'm really struggling to use my measure to graph by fruits that are in groups of more than 1 in my Unique Fruit Count.

1 ACCEPTED SOLUTION
talespin
Solution Sage
Solution Sage

hi @pbi_girl_21 

 

You can use these Measures.

 

Fruit Count =
VAR _FruitCount = CALCULATE(DISTINCTCOUNT(Fruits[Product]), Fruits[Fruit Indicator] = 1)

RETURN IF( ISBLANK(_FruitCount), 0 , _FruitCount)
 
 
Fruits =
VAR _FruitCount = [Fruit Count]
RETURN IF(_FruitCount = 1, CALCULATE( VALUES(Fruits[Product]), Fruits[Fruit Indicator] = 1) , BLANK())
 
 
You can also show all the fruits in a Store if that is the requirement.
talespin_1-1709191822942.png

 


 

View solution in original post

4 REPLIES 4
talespin
Solution Sage
Solution Sage

hi @pbi_girl_21 

 

You can use these Measures.

 

Fruit Count =
VAR _FruitCount = CALCULATE(DISTINCTCOUNT(Fruits[Product]), Fruits[Fruit Indicator] = 1)

RETURN IF( ISBLANK(_FruitCount), 0 , _FruitCount)
 
 
Fruits =
VAR _FruitCount = [Fruit Count]
RETURN IF(_FruitCount = 1, CALCULATE( VALUES(Fruits[Product]), Fruits[Fruit Indicator] = 1) , BLANK())
 
 
You can also show all the fruits in a Store if that is the requirement.
talespin_1-1709191822942.png

 


 

v-jialongy-msft
Community Support
Community Support

Hi @pbi_girl_21 

 

Try use filters to filter your unique fruit count field

vjialongymsft_0-1709001018528.png

 

 

 

 

 

Best Regards,

Jayleny

 

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

Thank you, this works for isolating Store B.

 

But it also made me realize I forgot to ask a part of my question. The next step I want to do is to be able to isolate for examples like the strawberry in Store A. Specifically, if a fruit is the only unique fruit for a store, how can I identify it? Filtering for 1 in the example above doesn't seem to work for me, it only gives me blank results.

Hi @pbi_girl_21 

 

 

Please try what I did in the screenshot.

vjialongymsft_0-1709171667334.png

 

 

 

Best Regards,

Jayleny

 

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors