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.

How to show trend ?

Discussion in 'Discuss Data Visualizations and Charting' started by mithil1, Aug 11, 2017.

  1. mithil1

    mithil1 Member

    Messages:
    50
    I am trying to make a dashboard that shows the trend. I am not able to get a right trend based on the numbers. Please look the attached screen shot. Below is the example

    There is a hotel chain named X. X has various locations across the country and its divided in East and West regions. further X operates under different names at each location. I am trying to see the monthly trend of hotel occupancy. Unfortunately since it operates under various names,I am not able to generate a proper chart. Take a look at what I am getting in the screen shot.

    I get a proper trendline if its just one location, but because there are multiple locations under different names, i dont get it.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,532
    Can we have a sample file please?
  3. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,463
    Can you add helper column that ties all the different names used by X into one group name? That should help solve your issue.
  4. mithil1

    mithil1 Member

    Messages:
    50
    here you go!

    Attached Files:

  5. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,463
    Still a bit confused about your expected end result.

    But typically, I'd use PivotTable to analyse these type of data.
    upload_2017-8-11_10-29-35.png

    Note: I assumed all data is for hotel chain X, and I arbitrarily picked East/West for demonstration.

    Attached Files:

    Thomas Kuriakose likes this.
  6. mithil1

    mithil1 Member

    Messages:
    50
    Thanks for the quick response. I will work on it. At my end when I checked of Text Axis in format axis, its able to generate a graph. But your solution looks better. I am gonna try.

    Another question is how can I link two data sets in the dashboard for e.g the common factor between two data sets is the city names and the region. Whats happening now is I have a column chart for other data where I have already made a dashboard with cities.
  7. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,463
    Depends on Excel Version.

    If you have PowerQuery & PowerPivot, I'd build relationship in data model.

    Otherwise, if you have Excel 2010 or later, use data relationship
    https://support.office.com/en-us/ar...ta-Model-533dc2b6-9288-4363-9538-8ea6e469112b

    You may need to create Lookup table that holds list of unique cities and related regions.

    Then base both pivot table off of the data model and use slicer to control both.
    Thomas Kuriakose likes this.
  8. mithil1

    mithil1 Member

    Messages:
    50
    Thanks. So finally I was able to get it running but since there are two charts 1) bar 2) trend . Some cities correctly displays the bar chart but doesn't show the trend line. That part is just empty or a dot.

Share This Page