Jump to content


Photo

OLAP Pivot Table Items wrongly listed

Pivot Table

  • Please log in to reply
4 replies to this topic

#1 jjjjjjjjjjjj

jjjjjjjjjjjj

    Member

  • Members
  • PipPip
  • 14 posts

Posted 29 February 2016 - 10:11 AM

Hi all,

 

I have encountered a problem when I create power pivot table ( same situation in pivot table with relationship function )

 

I have first table defined as

 

StateNo    State

01             NSW

02             QLD

03             VIC

 

and second table defined as

 

Branch      StateNo

Branch-1   01

Branch-2   01

Branch-3   02

Branch-4   02

 

and third table

 

Branch       Rev

Branch-1    $90

 

 

now I have linked State to StateNo in first and second tables

 

my question is when I draw State (first table) and Branch (second table) in pivot table Row area, it would wrongly show

NSW

   Branch-1

   Branch-2

   Branch-3

   Branch-4

QLD

   Branch-1

   Branch-2

   Branch-3

   Branch-4

VIC

   <SAME THING ABOVE>

 

 

it may be fixed only when I draw Rev (third table) into Value area in Pivot, but It just show Branch-1. I want to show all branches in second table because manager need to know who get 0 this month.

when I choose "show items with no data on row" it would show that stupid layout again

 

Ideally,

it should give me follow display

NSW

   Branch-1   $90

   Branch-2   0 (or blank)

QLD

   Branch-3  

   Branch-4

 

hopefully I have explain clearly.

 

thanks for instruction.

 

cheers.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 29 February 2016 - 07:12 PM

Hi jjjjjjjjjjjj,

 

This happens because of the way that pivot tables constrain results. By default, they only show items that have values (specified by the Values field).

 

In this case, values only exist for Branch1, so it is the only item shown in the pivot table. You can work around this by including StateNo under Values - all branches contain a StateNo, so this will have the effect of forcing the pivot table to display all of them. You can then hide the column containing the StateNo.

 

I've attached an example workbook using your specifications:

 

Attached File  OLAP Relationships.xlsx   155.33KB   55 downloads

OLAP Relationships.png

 

For anyone looking to create a similar system, you can see an introduction to OLAP pivot tables in the video lesson: Expert Skills Lesson 6-2 Create a simple data model.

There are also many more video lessons explaining Excel's OLAP, MDX and BI features in the rest of: Session 6: The Data Model, OLAP, MDX and BI.

 

I hope this provides the solution you were looking for, but please feel free to reply if you're still having trouble achieving what you need.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 jjjjjjjjjjjj

jjjjjjjjjjjj

    Member

  • Members
  • PipPip
  • 14 posts

Posted 02 March 2016 - 09:40 AM

Thank you Administrator!

 

Now I know, it seems be a must to have a field in value area.

 

I am in the process of updating reporting system of my weekly work from formula based to Powerpivot table based. Excel 2016 provides more charts and Data query for getting data. That is so amazing.

 

I really hope you can start Excel 2016 soon and put more emphasis on DAX and Pivot presenting.

 

anyway, thank you for clear explanation.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 02 March 2016 - 10:54 PM

Hi jjjjjjjjjjjj,

 

Just as an additional note, it's also possible to configure a pivot table to show all items, even if they don't have values.

 

To configure your pivot table to shown items with no data, use the following steps:

  1. Click PivotTable Tools > Analyze > Options.
     
  2. Click the Display tab.
     
  3. Check the Show items with no data on rows checkbox.
     
  4. Click OK.

Unfortunately this doesn't quite have the effect you want, because all branches are shown for all states, irrespective of the relationships that you have set up. It isn't a good solution in this case, but might be useful in other pivot tables that you create.

 

I've attached a new example showing this approach:

 

Attached File  OLAP2.xlsx   155.36KB   53 downloads

OLAP2.png

 

I hope this is useful information, even if you do ultimately stick with the original implementation.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 jjjjjjjjjjjj

jjjjjjjjjjjj

    Member

  • Members
  • PipPip
  • 14 posts

Posted 02 March 2016 - 11:55 PM

Well I think this second is not what I want. Because Branch-3 and 4 is not from NSW. In report it demonstrates a wrong relationship.

 

but first reply is useful.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users