cancel
Showing results 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.

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:

 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.

1 ACCEPTED SOLUTION
Solution Sage

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.

4 REPLIES 4
Solution Sage

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.

Community Support

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.

New Member

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.

Community Support

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.

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors