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.

Need a Dashboard

Discussion in 'Discuss Data Visualizations and Charting' started by dieselkhan, Dec 12, 2017.

  1. dieselkhan

    dieselkhan Member

    Messages:
    302
    I need a dashboard which need to compare with last year and this year and per month total per Agent there is two total sale one is total sale and 2nd who bring new business in company so i need both separate if any one can help me out with mind blowing dashboard would be great ;)

    and please if you can avoid Pivot table would be Great otherwise should be okay

    Attached Files:

  2. chirayu

    chirayu Well-Known Member

    Messages:
    887
    I've done a Year On Year (YOY) calculation example but you can use the same methods to create a Month on Month (MOM) file. I've added some reference links in the file in case you don't know how to do the things I have done.

    Attached Files:

    Thomas Kuriakose and dieselkhan like this.
  3. dieselkhan

    dieselkhan Member

    Messages:
    302
    Thanks ..... one more question
    Total sale is 4292 and Lost sale 3005 so i need to find what is the percentage of total sale and what is the percentage of lost sale thnaks
  4. chirayu

    chirayu Well-Known Member

    Messages:
    887
    Your totals are incorrect. Attached updated

    Attached Files:

  5. dieselkhan

    dieselkhan Member

    Messages:
    302
    Thanks Chirayu
    im getting #Value error when i tried to make dashboard for MOM
    im useing this formula

    =IFERROR((SUMIFS(Data!$E:$E,Data!$J:$J,MOM,Data!$A:$A,$A2)-SUMIFS(Data!$E:$E,Data!$J:$J,MOM-1,Data!$A:$A,$A2))/SUMIFS(Data!$E:$E,Data!$J:$J,MOM-1,Data!$A:$A,$A2),"")
    xxtino likes this.
  6. chirayu

    chirayu Well-Known Member

    Messages:
    887
    attached

    Attached Files:

    xxtino and Thomas Kuriakose like this.
  7. dieselkhan

    dieselkhan Member

    Messages:
    302
    @chirayu if i want filter as type as well i tried with and funcation but not success if you dont mind if you can

    Attached Files:

    xxtino likes this.
  8. dieselkhan

    dieselkhan Member

    Messages:
    302
  9. chirayu

    chirayu Well-Known Member

    Messages:
    887
    Looking at your file you haven't done any formula changes.

    Attached is the file with Type added in. I had to edit the type column in the Data sheet because "Electricity" had a space in the text so formula wasn't reading it from the dropdown.

    You can also do another drilldown based on Quarter e.g. Q1,Q2,Q3,Q4 where Q1 = Jan, Feb, Mar, Q2 = Apr, May, Jun, Q3 = Jul, Aug, Sep, Q4 = Oct, Nov, Dec. But that's easy enough to do yourself as explained in the file. Unless you are trying to use dependent dropdowns.

    Also I'd suggest you learn the IF, AND, SUMIFS formulas as well as using Conditional Formatting/ Named Ranges, otherwise you'll have a problem editing the file in the future if there are more agents that need to be added/ file needs to be edited for more criteria

    Attached Files:

    dieselkhan likes this.
  10. dieselkhan

    dieselkhan Member

    Messages:
    302
    Thanks .. yes im learning all those formulas

Share This Page