cancel
Showing results for
Did you mean:
Regular Visitor

Measure - Count number of zero

Hi,

I am totally lost on how to count when the count is dependent on a sum, and not just a value.

1. How do I count the number of 0 sales for Product Category per Store in table below. The way I am thinking, it should be 2 in this example (Star|Alfa and Star|Bravo).

2. How do I count the number of Stores with 0 sales on one or more Product Category? The way I am thinking, it should be 1 (Star)

 Store Product Category Product Sales Star Alfa A1 0 Star Alfa A2 0 Star Bravo B1 0 Star Bravo B2 0 Moon Alfa A1 0 Moon Alfa A1 8 Moon Bravo B2 10 Moon Bravo B2 0

I would deeply appreciate any input.

/depple

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

Re: Measure - Count number of zero

Hi @depple

I advice you to create, two dimensions, one for product and one for stores:

and then you can have:

```Measure =
COUNTROWS(
FILTER(
SUMMARIZE( Data, Products[Product Category], Stores[Store] ),
CALCULATE( SUM( Data[Sales] ) ) > 0
)
)```
```Measure2 =
COUNTROWS(
FILTER(
SUMMARIZE( Data, Stores[Store] ),
CALCULATE( SUM( Data[Sales] ) ) > 0
)
)```

Proud to be a Datanaut!

Super User

Re: Measure - Count number of zero

Hi,

This measure works

Measure = CALCULATE(DISTINCTCOUNT(Data[Store]),FILTER(SUMMARIZE(Data,Data[Store],Data[Product Category],"ABCD",SUM(Data[Sales])),[ABCD]=0))

4 REPLIES 4
Super User

Re: Measure - Count number of zero

Hi @depple

I advice you to create, two dimensions, one for product and one for stores:

and then you can have:

```Measure =
COUNTROWS(
FILTER(
SUMMARIZE( Data, Products[Product Category], Stores[Store] ),
CALCULATE( SUM( Data[Sales] ) ) > 0
)
)```
```Measure2 =
COUNTROWS(
FILTER(
SUMMARIZE( Data, Stores[Store] ),
CALCULATE( SUM( Data[Sales] ) ) > 0
)
)```

Proud to be a Datanaut!

Regular Visitor

Re: Measure - Count number of zero

Hi @LivioLanzo,

Thank you so much for your help! I understand that I have a long way to go in learning how Power BI works.

With a tiny tweak, I got measure 1 working just fine!

On measure 2 I am struggling a little. As far as I can observe, it counts the number of Store with 0 sales in all Product Category (zero sales totally), but I need it to count number of Store with 1 or more zero Product Categories with zero sales. In other words, Store that do not sell in any Product Category. Do you think it is possible?

Super User

Re: Measure - Count number of zero

Hi,

This measure works

Measure = CALCULATE(DISTINCTCOUNT(Data[Store]),FILTER(SUMMARIZE(Data,Data[Store],Data[Product Category],"ABCD",SUM(Data[Sales])),[ABCD]=0))

Highlighted
Regular Visitor

Re: Measure - Count number of zero

Hi @LivioLanzo,

It certainly did work!

Thanks you so much.

/depple