Counting Products based on Indicator

[pbi_girl_21]

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:

Store	Product	Fruit Indicator
Store A	Beans
Store B	Strawberry	1
Store C	Lettuce
Store A	Strawberry	1
Store B	Banana	1
Store B	Strawberry	1

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:

Store	Unique Fruit Count
Store A	1
Store B	2
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.

[talespin · Accepted solution]

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.

[v-jialongy-msft]

Hi @pbi_girl_21 

Try use filters to filter your unique fruit count field

Best Regards,

Jayleny

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

[pbi_girl_21]

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.

[v-jialongy-msft]

Hi @pbi_girl_21 

Please try what I did in the screenshot.

Best Regards,

Jayleny

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