chart labels

[Tauqeer]

I am attaching my dashboard , one of the chart I have is portfolio vs segment, segment is what I select from the slicer and the chart shows portfolio and selected segment approval rate on the chart which you can see with green and yellow lines. I am wondering if it is possible when any slicer filter is selected the yellow line labels show that it is that particular segment is selected vs portfolio label and when no slicer filter is selected it only shows the portfolio labels.

Is it possible, please advise.

Also the portfolio vs segment chart, portfolio data series is static B43:C53, as it remains
to be static to be compared to segment series, how can it make it flexible when the new
data hits the file. any suggestions?

Any general advise to improve the dashboard will be highly appreciated as well.

thanks

TK

 

[GraH - Guido]

You can add an extra data series that only shows the label value of the selected slicer. This line has no colour, but a distinct marker. The labels are plotted on this "dummy" series. Use an IF statement that returns NA () for non selected values.
I wanted to make an example for you, but I'm in the dark about which slicer is about segment. Is it App types?

Dynamic data: you could refer to the pivot range and name the range.

Dashboard:
- charts are rather big. And perhaps over formatted. Example: the second % axis is redundant, since you have labelled the line chart.
- some chart types are cluttered. Can easily understand the message of "Refer Reason Volume"? or "App Type Volume"?
- Does everything need to be a chart?
- Do you need all the slicers?
- Slicers settings: why not set use more columns? Like so:


I wanted to help more and suggest more, but I need to go for now.

 

[Tauqeer]

Hi
Thanks for your response , segment is like
App type, state , capacity etc.

I am confuded with the dummy series, could
You please advise on that.

All these charts are the requirement at this stage
To capture all the elements in the portfolio.

I will be glad if you can suggest any other
Types of charts which can further enhance
It.

Regards
Tk

 

[GraH - Guido]

Hi Tk, since segment is more then 1 data series, things become more complicated. I'm not sure about how to accomplish "that particular segment is selected vs portfolio label" visualisation. I do not, huh, "see" how...
The "dummy" data series is explained here https://peltiertech.com/conditional-formatting-of-excel-charts/.
and/or here as well https://chandoo.org/wp/2017/10/09/conditional-formatting-chart-data-labels/.
Dashboards are more about telling a story, enabling a management decision, then just showing a bunch of charts, though each also should tell you something insightful. I somehow miss the story/message you are trying to get across.
Perhaps read trough these blog posts:
https://chandoo.org/wp/excel-dashboards/
https://chandoo.org/wp/2010/04/19/chart-selection-process/
https://chandoo.org/wp/2016/04/06/all-versions-of-truth/.
https://chandoo.org/wp/2012/08/02/making-dashboards-interactive/

The master truly explains it way better then I ever could.