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.

Tracking System

Discussion in 'Excel Dashboards' started by dieselkhan, Feb 9, 2018.

  1. dieselkhan

    dieselkhan Member

    Messages:
    265
    Anyone can please help me out in creating eye catching Dashboard with all calculation Please

    • Number of Suppliers (breakdown with Contract type) each month (Total,Percentage)
    • Value of sale by Supplier (breakdown with Contract type) each month (Total,Percentage)
    • if any supplier percentage is less than 5 so it will go automatic in "Other" Category which we need to make manually. (I hope you understand what my mean is)
    please find the attached file


    Thanks

    (Total,Percentage)

    Attached Files:

  2. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Hi dieselkhan, can you make a proposal on how the dashboard should look like? A kind of mock-up. Sorry to say, I find it rather too easy to ask "make me an eye catching dashboard". At least you should define the kind of visualization you are after.
    Above are just some summary tables if I understand correctly. Those are simply made with some pivots (see attached file if that is suitable). I do not call that a dashboard.
    I notice you use Power Query. The summary tables can be made in PQ with group by and some calculated columns. And no need for pivots. And that would already give it a nicer look.

    Attached Files:

  3. dieselkhan

    dieselkhan Member

    Messages:
    265
    Thanks sorry for missguide

    i'm looking for pie chat and breakdown by contracttype
    Last edited: Feb 9, 2018
  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Pie chart? For each month? That does not sound advisable. It would be difficult to see an evolution over the periods. What is a composition if you have like 7 slices per pie chart? Or more...
    I still have the same question: what kind of visual you need? How many charts/tables? Which type? How do you want to see/use the breakdown? Does it need to be interactive?
    So many alternatives are possible: charts, conditional format, pivots, pivot charts, tables, selection buttons (radio/check box), slicers, sparklines,...
    Look at some of the dashboard and or chart examples on the blog pages of :awesome:. Inspiring!
    This could be a nice little project, given more specific requirements.
    I added 2 conceptual ideas in the file.

    Attached Files:

  5. dieselkhan

    dieselkhan Member

    Messages:
    265
    Thanks

    but there is the one problem
    in Gas Contracttype
    Utilita
    supplier have 2.47% why this
    Utilita
    not going to add in other category

    see the image plz

    Attached Files:

    Last edited: Feb 9, 2018
  6. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    I did say conceptual... While the calculation I quickly added in your table is like you want it. Utilia is in the group Other. But it is a fixed calculation.
    upload_2018-2-9_17-40-48.png
    So the kind of visual you want is like in the picture? And is is coordinated with the slicer. This means the calculation needs to be dependent of the slicer selection. That is why it is not working. But is is feasible.
  7. dieselkhan

    dieselkhan Member

    Messages:
    265
    can i send you fila which i create and you fix for me as im new in excel
  8. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    You can use dropbox or so to share the file and post the link here.
  9. dieselkhan

    dieselkhan Member

    Messages:
    265
    Please find the attached file

    Attached Files:

  10. dieselkhan

    dieselkhan Member

    Messages:
    265
    @grah-gudo are you there
  11. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Hi dieselkhan, sorry I haven't had the time yet. But I did not forget. Bear with a few moments more, please.
  12. dieselkhan

    dieselkhan Member

    Messages:
    265
  13. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Perhaps like this?
    I added a pivot to know the slicer selection.

    Attached Files:

  14. dieselkhan

    dieselkhan Member

    Messages:
    265
    Still giving figuers which have < 5%

    Attached Files:

    Last edited: Feb 12, 2018 at 12:51 PM
  15. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Strange, I don't get that. Did you click the slicers?
    upload_2018-2-12_14-36-8.png
  16. dieselkhan

    dieselkhan Member

    Messages:
    265
  17. dieselkhan

    dieselkhan Member

    Messages:
    265
    i just refresh the data andthen its give me this
  18. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Alternative... Charts with Dynamic ranges based on pivots responding to slicers.
    I copied your data to a new workbook and worked from there. Somehow your PQ statement kept throwing error messages at me.
    I made one example of the charts like you want it. Some work for you is to copy the solution for the other charts.

    Attached Files:

  19. dieselkhan

    dieselkhan Member

    Messages:
    265

    Thanks ATM its working fine .. but graph have problem if number is more thn 5 then its not showing in graph and if less then 3 so graph like ugly
  20. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Again strange, since I made it the table as such it is working for up to 10 values.
    In my version that is working fine. Lay-out remains clean.
    Tried it with a previous version of Excel and labels were all around the place. Is that the your issue as well?
    Labels would be stable if you were using bar or column chart.
  21. GraH - Guido

    GraH - Guido Active Member

    Messages:
    286
    Bar charts would allow keeping the labels in place. And are considered a better option above pie/doughnut charts when you have more then 3 categories.
    Alternatively, instead of showing above a % threshold, you could choose to show always a TOP X (5-7?). Which is also possible with Pivot. Not showing "others" though.

    Attached Files:

  22. dieselkhan

    dieselkhan Member

    Messages:
    265
    thanks Working love ya

Share This Page