Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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)
2. Create a measure for the card:
#ZeroColors =
COUNTROWS(
FILTER (
SUMMARIZE ( Sales, Sales[Color], "amt", SUM ( Sales[Sales] ) ),
[amt] = 0
)
)
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!
@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)
2. Create a measure for the card:
#ZeroColors =
COUNTROWS(
FILTER (
SUMMARIZE ( Sales, Sales[Color], "amt", SUM ( Sales[Sales] ) ),
[amt] = 0
)
)
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.
Hi @jer91899 ,
Please create a measure to count zeros and then use it in a visual:
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).
Hi @jer91899 ,
Try these two, whichever works best for you.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |