Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Calculate count of application which has blank sales based on category selections

Hello All,


I have data of applications and their sales for each category wise.

now to get the count of applications which has blank or 0 sales for each category wise i can directly do the DISTINCTCOUNT of applications and it will do the need.

But, the my customer is looking for a count in a different way.

Below is the sample data that i have.


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZVRa4MwFIX/ysXnDnSFMR+v1m7dVidm7TZKkZBaF6oGkshgv34O1tFq7Szx0XNyvpsTQlytrOspwpJLXdEcSEGlBp/KjTWyYpGnCgquFC8z4CXIWrhiVNNcZLVvW+vRyhq/34FH2U5st5ylv7GYq536W3I7AZz1BuL4xnXs+hMZE1WpVbeTfIokxrY/F3Fb/JmO9eisLNKT1PbGj6wu+f9Crms7zslCTadZaO8fFdqLnYUOFzQKHVpdcv9CBKOkMQABC/olSlim5UZI8OutSZonuOiP72J4sTnDR3PGJDBnBMScMR3gPGYv5oyHyJwxfzNnhE/mjGgABhngfiweB2CQM0/L2VD/wVGI4D0T//6SlIevBII4gjDVquI6vSgsFPsArLSAqP5bqX7Z9Tc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, Category = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Application", type text}, {"Category", type text}, {"Sales", Int64.Type}})
    #"Changed Type"

 Now the data looks as below image.


The ask is, I have total 8 categories of data, in which

When i dont filter or select any value from category, it should consider all the categories and show the applications which has zero or blank sales for all the categories.

in this case, the count of applications which has 0 or blank sales for all categories are 1 - A99011.

When i select accounts category, the count is 2 - A36910, A99011




But when i do not select any categories it shows the count as 22 sa i took distinct count of applications but in my case it should give only 1 as there is only one application which has all categories data as blank.


Please help.


Mahesh V.



Regular Visitor

@IIPowerBlog i have provided the power query with sample data.

It is the data that i am working with.

Regular Visitor

I have already provided the sample data for  powerquery...

Regular Visitor

Can anyone please help me on this..

Helper I
Helper I

hello @mahg1kor . Have you tried to select all categories? What happens then? You can simply do this in the slicer by showing the 'Select All' option and running your report by default on it. Does this help?



@IIPowerBlog the selection is taking the OR condition while doing the distinctcount and hence I am getting the 22 as count.

But it should be AND condition, so that, we are making sure that if all are selected means, all categories values should be 0 and give the count accordingly.

@IIPowerBlog thanks for the reply.

I did not uunderstood how select all can help me to get the distinct count of applications which has all categories as blank.

Even though i did it, still showing count as 22.


as i mentioned in the question, it should be 1, as there is only one application has all zero values for all the categories.

Eventually, what i am asking here is the other way of what power bi default feature does.

If you select all the categories means, it should give the count of all applications which has 0 for all the categories and if you select one or two or any, it should give accordingly.




Hope i made it clear for you.

Let me know if you need anymore details.



Mahesh V

hi again @mahg1kor  it looks like your data source is the reason. can you please share this so I can understand better? thank you. 

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.