cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
depple Regular Visitor
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)

 

StoreProduct CategoryProductSales
StarAlfaA10
StarAlfaA20
StarBravoB10
StarBravoB20
MoonAlfaA10
MoonAlfaA18
MoonBravoB210
MoonBravoB20

 

I would deeply appreciate any input.

 

/depple

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Measure - Count number of zero

Hi @depple 

 

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

 

Capture.PNGCapture2.PNG

 

 

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
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))
 
Untitled.png
4 REPLIES 4
Super User
Super User

Re: Measure - Count number of zero

Hi @depple 

 

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

 

Capture.PNGCapture2.PNG

 

 

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

depple Regular Visitor
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
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))
 
Untitled.png
Highlighted
depple Regular Visitor
Regular Visitor

Re: Measure - Count number of zero

Hi @LivioLanzo,

 

It certainly did work!

 

Thanks you so much.

 

/depple