Pivot Charting

[venky24]

Dear all
I have a data on basis which chart is created using pivot.
What I am trying is to maintin the output format irrespective of data

Let me clear with a simple example. There are two divisions-EAST & NORTH
EAST has placed orders for PC PRINTER and SCANNER
NORTH has placed an order for PC and LAPTOP.

so if i create a chart / table without any filterarion on basis state it would show
PC PRINTER LAPTOP SCANNER and related values below

PC PRINTER LAPTOP SCANNER
EAST 9.... 100..... ...... 87
NORTH 9.... ...... 100 .....

Now in event I filter on basis EAST, I output get would be as below

PC PRINTER SCANNER
EAST 9 ..... 100 ..... 87

However I want it to be as
PC PRINTER LAPTOP SCANNER
EAST 9 ..... 100 ..... ..... 87

Can this be done ??? Please guide. There are multiple items and 5 regions so I want to maintain the formats in presentation... one way is to include dummy records.. but as said there are multiple items and then it becomes a tedious task. Please help

 

[Chihiro]

Upload sample file with raw data, along with desired output structure.

Also indicate version of Excel that you use.

 

[venky24]

for sake of better clarity, i have annexed the sample data, charting and expected output. Trust is more clarrified

 

[Chihiro]

Ok, I see what you mean.

Right click on Row Label field (Item).
Field settings->Layout & Print

Check "Show items with no data".
Do same for Type.

 

[Chihiro]

Oh, I'd also recommend setting value axis (y axis), scale manually, to be fixed value. To ensure same scaling regardless of chart filter.

Ex: Use =Ceiling.Math(Max(Sum of Count values), 5) as max.

 

[vletm]

venky24
.. or something like this in 'Sample Data'

 

[venky24]

Thankyou !! it helped a lot.

Oh, I'd also recommend setting value axis (y axis), scale manually, to be fixed value. To ensure same scaling regardless of chart filter.

Ex: Use =Ceiling.Math(Max(Sum of Count values), 5) as max.

 

[venky24]

Yes, i had this. But what i wanted is to have the categories shown, even if values were 0. Eitherway, thanks for help.

venky24
.. or something like this in 'Sample Data'

 

[RedHotSun]

venky24
.. or something like this in 'Sample Data'

Though your visualization looks neat & appealing, please have a look at the Laptop data. The total cannot be less than it's elements. Probably you would want to make both axes equal.

 

[RedHotSun]

Something like this..

 

[vletm]

RedHotSun
Of course, it would be better to have both axel equal, if needed.
(There are values for all bars, which should give correct image too.)
That is detail for me - no needed to solve with the 1st version.
You original expect was different - same texts many times.

After You wrote
But what i wanted is to have the categories shown, even if values were 0.
... and I didn't continue to do any modification.

There could be more things to do,
all depends Your needs - not mine.