• 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.

Tracking System

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)
 

Attachments

GraH - Guido

Well-Known Member
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.
 

Attachments

GraH - Guido

Well-Known Member
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.
 

Attachments

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
 

Attachments

Last edited:

GraH - Guido

Well-Known Member
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.
 

GraH - Guido

Well-Known Member
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.
 

Attachments

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.

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
 

GraH - Guido

Well-Known Member
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.
 

GraH - Guido

Well-Known Member
graph have problem if number is more thn 5 then its not showing in graph and if less then 3 so graph like ugly
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.
 

Attachments

Top