• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Charting

venky24

New Member
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
 
Upload sample file with raw data, along with desired output structure.

Also indicate version of Excel that you use.
 
for sake of better clarity, i have annexed the sample data, charting and expected output. Trust is more clarrified
 

Attachments

  • pivot chart query.xlsx
    72.3 KB · Views: 4
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.
 
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.
 
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.
 
Back
Top