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
jer91899
Advocate I
Advocate I

Count of Zero Sums

Hi, can you help me count the rows where an aggregated sum is zero? 

 

I have tried filtering by zeros but this leads to incorrect aggregated (sum) counts. PBIX file is linked here: https://drive.google.com/file/d/1JspcYlTWguK6-bIT7S87yn1aFAznbnaP/view?usp=sharing

 

jer91899_0-1620744902726.png

 

1 ACCEPTED SOLUTION
ERD
Super User
Super User

@Hi @jer91899 ,

Here is one of the options:

1. Create a measure and use it for filtering the first table:

#IsZero = IF(SUM(Sales[Sales])=0, 1, 0)

ERD_2-1620752021628.png

2. Create a measure for the card:

#ZeroColors = 
COUNTROWS(
    FILTER (
        SUMMARIZE ( Sales, Sales[Color], "amt", SUM ( Sales[Sales] ) ),
        [amt] = 0
    )
)

ERD_3-1620752177751.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

5 REPLIES 5
ERD
Super User
Super User

@Hi @jer91899 ,

Here is one of the options:

1. Create a measure and use it for filtering the first table:

#IsZero = IF(SUM(Sales[Sales])=0, 1, 0)

ERD_2-1620752021628.png

2. Create a measure for the card:

#ZeroColors = 
COUNTROWS(
    FILTER (
        SUMMARIZE ( Sales, Sales[Color], "amt", SUM ( Sales[Sales] ) ),
        [amt] = 0
    )
)

ERD_3-1620752177751.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Thank you, @ERD . ZeroColors worked perfectly. Using this measure, I am able to filter by '1' in the table to show only zeroes, and to use this same measure in the card.

jer91899_0-1620762718264.png

 

dkaushik
Resolver II
Resolver II

Hi @jer91899 ,

 

Please create a measure to count zeros and then use it in a visual:

Zero Sales = CALCULATE(COUNT(Sales[Sales]), Sales[Sales] = 0)
 
If this post helps, then please consider Accept it as the solution and give thumbs up to help the other members find it more quickly.
 
Thanks,
Dheeraj

Thank you for the quick response, Dheeraj @dkaushik 

 

This measure counts individual zero sales but I am looking for distinct count of colors with zero sales. The count card should show 1 (not 9).

jer91899_1-1620749889269.png

 

 

Hi @jer91899 ,

 

Try these two, whichever works best for you.

Distinct count of color with no sales = CALCULATE(DISTINCTCOUNT(Sales[Color]), Sales[Sales] = 0)
Distinct count of sales with no sales = CALCULATE(DISTINCTCOUNT(Sales[Sales]), Sales[Sales] = 0)
 
 

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.