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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mahg1kor
Frequent 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.

 

let
    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}})
in
    #"Changed Type"

 Now the data looks as below image.

mahg1kor_0-1673874528677.png

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

mahg1kor_2-1673874916763.png

 

mahg1kor_1-1673874881865.png

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.

mahg1kor_3-1673874949311.png

Please help.

Thanks,

Mahesh V.

 

 

8 REPLIES 8
mahg1kor
Frequent Visitor

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

It is the data that i am working with.

select Application,COUNT(Indicator) IndCnt, SUM(ISNULL([Day-01],0)) sm
from Report Check Data
group by Application
Having --COUNT(Indicator) = 8 and
SUM(ISNULL([Day-01],0)) = 0
order by 2 desc

this is my sql query 
i need to convert this into Power Dax, can anyone please help me on this.

 

mahg1kor
Frequent Visitor

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

mahg1kor
Frequent Visitor

Can anyone please help me on this..

IIPowerBlog
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_0-1673884018402.png

 

@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.

mahg1kor_0-1673885364250.png

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.

 

Regards,

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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.