cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillEyedowin Frequent Visitor
Frequent Visitor

Count Blanks Table

Hi All,

 

I would like to make a table in a report which counts whether a store has had 0 sales (blank/lack of data in this case) in a defined period of time.

 

The main problem I have is the dataset does not contain any information if a product in a store has not sold anything on that day. - Perhaps there is an easy way to populate my sold dataset with blank data if there are no sales?

 

Therefore I created a measure to check if on a certain day there were sales -> if not then fill that row with 0.

Current Table.PNGCurrent Table - need each date in row for measure to work properly

measure.PNGMeasure to determine blank data on that day = 0 sales

However, this requires each date/day to be included in the table for the measure to work as intended.

 

I would like the table to work as pictured; where there is a single line for a store which will count how many 0 sales days it has had and then the deliveries it has had in that time(can do that already). I also don't want to have the date column in the table if possible.

desired table.PNGDesired table to look like thisRelationships.PNGRelationship Tablesold table.PNGThe sold table - showing when a product in a store sells(no data if no sale on that day)

Let me know if there's anything else I can provide to help solve this problem

 

Thank you in advance 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count Blanks Table

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

6 REPLIES 6
Ross73312 Super Contributor
Super Contributor

Re: Count Blanks Table

Add plus zero to the end of your formula.

 

i.e.

Sales = SUM(Sold[Quantity Sold]) + 0

 

As a side note, i notice you are using Calculate but for no purpose. You can remove that from your formula.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Super User

Re: Count Blanks Table

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
WillEyedowin Frequent Visitor
Frequent Visitor

Re: Count Blanks Table

Super User
Super User

Re: Count Blanks Table

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


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

View solution in original post

WillEyedowin Frequent Visitor
Frequent Visitor

Re: Count Blanks Table

Hi,

 

Looks like that worked - could you please explain your solution a little so I know exactly what you did?

 

Is this measure the only thing you changed?

 

Zero Sales Store = COUNTROWS(FILTER(SUMMARIZE(VALUES(DateKey[Date]),[Date],"ABCD",SUM(sold[Quantity Sold])),[ABCD]=0))
 
Thanks
Super User
Super User

Re: Count Blanks Table

Hi,

 

Yes. That is all i changed.  The VALUES(DateKey[Date]) portion creates a unique list of all dates from the DateKey Table.  Against every date, we then plot the sale and give a title to this column as ABCD.  We thereafter filter the ABCD column on all values where the value is 0.  Finally we count the days in the filtered Table.

 

If my reply helped, please mark it as Answer.


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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 871 guests
Please welcome our newest community members: