Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
depple
Helper III
Helper III

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
LivioLanzo
Solution Sage
Solution Sage

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!  

View solution in original post

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

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!  

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?

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @LivioLanzo,

 

It certainly did work!

 

Thanks you so much.

 

/depple

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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