1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Pivot Charting

Discussion in 'Discuss Data Visualizations and Charting' started by venky24, Aug 15, 2018.

  1. venky24

    venky24 New Member

    Messages:
    7
    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
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,929
    Upload sample file with raw data, along with desired output structure.

    Also indicate version of Excel that you use.
    venky24 likes this.
  3. venky24

    venky24 New Member

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

    Attached Files:

  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,929
    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.
    upload_2018-8-15_15-10-51.png
    Thomas Kuriakose likes this.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,929
    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 and Thomas Kuriakose like this.
  6. vletm

    vletm Excel Ninja

    Messages:
    4,031
    venky24
    .. or something like this in 'Sample Data'

    Attached Files:

  7. venky24

    venky24 New Member

    Messages:
    7
    Thankyou !! it helped a lot.

  8. venky24

    venky24 New Member

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

  9. RedHotSun

    RedHotSun New Member

    Messages:
    3
    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.
  10. RedHotSun

    RedHotSun New Member

    Messages:
    3
    Something like this..

    Attached Files:

  11. vletm

    vletm Excel Ninja

    Messages:
    4,031
    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.
    sathishsusa likes this.

Share This Page