How to Show all Columns for a DataSet When No Data Exists for That Row/Column Intersection

Question

I'm building some pivot tables off of some PowerPivot data. I have charts that I want to create off the pivoted data where the X-Axis are months and the Y Axis is some value. I have my DAX filter only return the last 12 months of calendar data because we only want to chart the last 12 calendar months. The issue is when there are no values for some of the months. In those cases the pivot table ends up only showing the months that have data. I need the pivot to show ALL 12 months and then put 0's in the months with no data. I've tried changing the pivot table options to show blank values with 0's, but that does not do it. I want the charts to tie to the pivot table instead of trying to use a bunch of vlookups on the pivoted data to create the 12 month charts.

How can I show all of the columns even when there is no data for that column?

 

Answer 1

Are you using a Date table?

Please show the formula for the measure.

Answer 2

=SUMX(FILTER(RFC_Error_Totals_By_Subsystem_Program_ALL,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),RFC_Error_Totals_By_Subsystem_Program_ALL[SUMTHECOUNT])

 

I list each collection month with how many months it is different from the very last collection month. I.e. If Dec 2011 is the last collection, Nov 2011 has a datediff of 1, Oct 2011 has a datediff of 2, etc.

Answer 3

Please try to add 0+ at the begining.

=0+ SUMX(FILTER(RFC_Error_Totals_By_Subsystem_Program_ALL,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),RFC_Error_Totals_By_Subsystem_Program_ALL[SUMTHECOUNT])

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 4

Did not work.

Answer 5

If you are not currently using a Date table then you might want to try adding a side table with months, relating it to your fact table on month and then use the month column in the related table as your x-axis. The months with no data don't show up because of MDX autoexists. See the link below with a related issue.

http://www.powerpivotblog.nl/powerpivot-dax-case-compare-product-sales-by-months-since-product-introduction

Answer 6

I tried that as well. The RELATED(Act_Rel_Date_Lookup[DATEDIFF]) is a table with all of the dates. Even using that as the x-axis made no difference.

Answer 7

Hi Whalensdad

Have you tried detecting for BLANK()?

Suppose your formula above is called [Values].  You could then use the following

=IF([Value] = BLANK(), 0, [Value])

And then use this new calculated column in your chart.  It should display zero for the months with no data

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 8

I don't know why you marked this as the answer. It did not work.

=if(Error_Totals[Last 12 Months] = Blank(),0,Error_Totals[Last 12 Months]) retrurned only those months with values.



Answer 9

Forget the chart for a second. What does the underlying pivot table show? Does it show zero for those months?

Answer 10

I haven't even created the chart. The pivot table only shows the months with data. As an example:

                  3/1/2011               7/1/2011               9/1/2011

Value 1           0                            8                           2

Value 2           1                            3                           0

Value 3           0                            0                           4

Answer 11

What if you create a measure TestZero:=0. What does this show? Does it show up in your pivot table?

Answer 12

I'm not sure what you are suggesting. Can you please explain?

Answer 13

I am having a hard time understanding why Tatyana's answer did not work for you if you're using a related Date field in your pivot table's columns. My test file shows zero even for months where there is no data. Can you provide a sample file?

Answer 14

Unfortunately, due to corporate policy, I can't send a file.



Answer 15

Construct a sample file with some dummy data in it that illustrates the problem you are having.

...or did you mean that you can't send any files?

Answer 16

I can't send ANY files. The best I can do is descibe the 2 underlying tables.

Table 1 is the table with the dates. It has 3 columns. Column 1 is the Last day of the month. There is a row for each of the last 24 months. Column 2 is the difference between the current month and the date in the first column. So as an example:

Date                Date Diff

1/31/2012             0

12/31/2011           1

11/30/2011           2

Table 2 is the fact table. It contains the data I want to pivot. There are 3 columns of importance. Column 1 is the Date column. It contains a date that matches a date in the date table. Column 2 is an ID. It contains some identifcation for the data being aggregated. Column 3 is a sumation of how many records are in the original data set that match the ID and Date. So as an example:

Date                  ID                       SumCount

1/31/2012     AAA-1                            3

1/31/2012     AAA-2                            1

1/31/2012     AAA-3                            4

1/31/2012     BBB-1                            2

12/31/2011   AAA-2                            6

12/31/2011   AAA-3                            1

11/30/2011   AAA-1                            2

11/30/2011   BBB-1                            1

If I wanted to see the last 3 months of AAA my table would look like:

                    11/30/2011              12/31/2011             1/31/2012

AAA-1                   2                               0                             3

AAA-2                   0                               6                             1

AAA-3                   0                               1                             4

If I wanted to see BBB:

                     11/30/2011                  1/31/2012

BBB-1                   1                                  2

This is the best I can do.

Answer 17

OK. I agree, it seems that what you're trying to achieve is a bit tricky.

The formula that I used in my test file looks like this:

=SUMX(FILTER(Dates,Dates[DateDiff]<7),0+[SumValue1])

I am going to try and translate that into the names used in your model.

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF]<12), 
            0+[SUMTHECOUNT])

Let me know how that works.

Answer 18

Unfortunately the same result. here is the measure:

=SUMX(FILTER(Totals_By_Subsystem,RELATED(Act_Rel_Date_Lookup[DATEDIFF]) < 12 ),0+Totals_By_Subsystem[SUMTHECOUNT])

BTW I appreciate the help.

Answer 19

Why did you switch the filter parameters?

I am intentionally applying the filter to and iterating over the Date table and not the fact table.

Please try the formula exactly as provided and let me know your result.

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF]<12), 
            0+[SUMTHECOUNT])

Answer 20

I hadn't seen that you changed the measure paramters. So I tried:

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+[SUMTHECOUNT])

And I get "Cannot find column "SUMTHECOUNT"

So I tried

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+Totals_By_Subsystem[SUMTHECOUNT])

and I get "The value for column 'SUMTHECOUNT' in table 'Totals_By_Subsystem' cannot be determined in the current context"

So I tried

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+Related(Totals_By_Subsystem[SUMTHECOUNT]))

and I got "The column 'Totals_By_Subsystem[SUMTHECOUNT]' either doesn't exist or doesn't have a relationship to any table availabe in the current context"  Which it most definately does.

Answer 21

Sorry, I thought [SUMTHECOUNT] was a measure.

Please create a measure SumTheCountMeasure:=SUM(Totals_By_Subsystem[SUMTHECOUNT])

and then use that measure in place of [SUMTHECOUNT].

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),
            0+[SumTheCountMeasure])

...or maybe you could just wrap the column reference in a SUM. (not tested)

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),
            0+SUM(Totals_By_Subsystem[SUMTHECOUNT]))

Answer 22

I created a SumTheCountmeasure as you indicated and then tried:

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ),0+[SumTheCountMeasure])

And there was no difference in the result.

Answer 23

Well, it works for me and I'd be happy to send you my test file.

Answer 24

Also, please confirm (again) that the columns in your pivot table are from the Date field in Act_Rel_Date_Lookup (Date Table) and not fromTotals_By_Subsystem (Fact Table).

Answer 25

Eureka! I was using the date column from the Fact table and not the lookup table. Somehow I missed that. Thanks for the assist. I ended up using

=SUMX(FILTER(Act_Rel_Date_Lookup,Act_Rel_Date_Lookup[DATEDIFF] < 12 ), 0+[SumTheCountMeasure])

along with changing the column and it works.

Thanks again.

Answer 26

Sweet!!!

"I love it when a plan comes together! " ~Hannibal (The A-Team)

Answer 27

those ways are way too difficult... EASIEST WAY:

Go to your Pivot Table's Field List

Go to the Column Labels--> click on whatever you're using as the columns ex. I used subinventory

Select Field Settings

Go to the Layout & Print Tab

And there is an option that say "show items with no data"

and THERE YOU GO! 

Solves issues without any formulas... :)