Reply
Highlighted
Frequent Visitor
Posts: 11
Registered: ‎09-03-2018
Accepted Solution

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 Smiley Happy


Accepted Solutions
Super User
Posts: 3,777
Registered: ‎01-14-2017

Re: Count Blanks Table

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

View solution in original post


All Replies
Super User
Posts: 1,802
Registered: ‎02-28-2017

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
Posts: 3,777
Registered: ‎01-14-2017

Re: Count Blanks Table

Hi,

 

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

Frequent Visitor
Posts: 11
Registered: ‎09-03-2018

Re: Count Blanks Table

Super User
Posts: 3,777
Registered: ‎01-14-2017

Re: Count Blanks Table

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Frequent Visitor
Posts: 11
Registered: ‎09-03-2018

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
Posts: 3,777
Registered: ‎01-14-2017

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.