Forum Discussion
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.
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.
4 Replies
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.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.
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.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
