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

Threshold lines in excel

PGK

New Member
Hi,


I have been trying to figure out how to create a threshold line in pivot charts for quite a while but haven’t been able to figure out how without having to manually enter the data

I am working on a very simple dashboard that shows service availability over a period of time, for different clients. The aim is to identify services that have breached priority (Eg- any service falling below a 95% line is breach of service agreement)

I have used slicers and timelines and have managed to create an interactive chart. But the issue I am facing is adding the threshold line that changes as you change a setting in the slicer

I have been trying to figure this one out for a long time and haven't been able to do so. Any help will be greatly appreciated

Thanks
 
I have tried that and it hasn't worked. Maybe I am not doing it right; I am not really used to using calculated fields. My knowledge of pivot tables and power pivot is extremely basic

I have attached the file that I have been working. It will be great if somebody can help me out with this

Thanks
 

Attachments

  • Service availability.xlsx
    23.1 KB · Views: 11
Hi,

Firstly, you need to design your chart properly & remove unnecessary data from series. Its a messy chart:confused:

Then it will be easier for someone to guide you properly.

Regards,
AM:)
 
IHi,

I am not really proficient in creating dashboards, but I have done my best. The thing is, due to the nature of the source data, the chart can get a bit confusing. This is the dummy data that I have created but the column names and the semantics are very similar to the data obtained from the source

Just to clarify, FALSE means services that have not breached SLA, and TRUE, vice versa. The reason for this is that the source application has a condition called "HAS BREACHED" which checks if it is TRUE or FALSE. I hope that makes sense ( It took a while for me to get my head around it as well)

The chart that I need should be dynamic such that changing the timeline and the business area changes the chart, and at the same time, there is a KPI target line streaking through the chart

I hope that clears it up a bit

Thanks

P.S: the KPI Target column that you see in the spread sheet is something that I manually put in. It is not available in the source data. The actual data is around has around 15 different Business Areas and total to around 4000 rows
 

Attachments

  • Service availability.xlsx
    31.6 KB · Views: 3
Guess this is what you are looking for, I have pulled the graph using formula and its dynamic, the formula will change as per the priority, month & year...
 

Attachments

  • Service availability (1).xlsx
    18.7 KB · Views: 7
Hi Nebu,

Thanks for your help. But is it possible to show it such that only specific time periods are displayed? Like in the file I have uploaded, selecting a specific range of months (like Jul, Aug, Sep) gives you the information only for that quarter

Also, could you please explain the code you have used? Thanks
 
Done , as per the selection u make in the pivot table the graph will change I have used name ranges to make the graph dynamic...
 

Attachments

  • Service availability (1).xlsx
    18.2 KB · Views: 15
thanks for your help, but this isn't how I was picturing it. I am not able to see the information for a specific time period like from January-Jun. Is there any other solution?

I am trying to use calculated fields but have no idea how to. Is there a way to display only specific columns without the data getting changed?
 
You can see the information by filtering the respective months in the pivot the graph will be autopopulated as per the selectio u make in the pivot, but be careful not to change the layout of the pivot.

Thanks
 
Back
Top