Measure - Count number of zero

[depple]

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

[LivioLanzo · Accepted solution]

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

[LivioLanzo · Accepted solution]

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

[depple]

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?

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

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

[depple]

Hi @LivioLanzo,

It certainly did work!

Thanks you so much.

/depple