cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zpaul_010 Regular Visitor
Regular Visitor

How to make a percentage target line using Line and stacked column chart?

So, Im trying to create a Line and stacked column chart of each technician and their pending status. So I want to show them on the chart and also incorporate our target which is 5% of the total

 

x-axis = technician field

column series = PendinStatus field (open, close)

target line= is 5% of each technician's total request

 

This means that target line should be relative to technician's number of closed request. 

 

Can anyone help me with the target line computation? Im really new to this so kinda slow. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

 

How's this for a Measure?

 

5% = CALCULATE(COUNT(Table1[Status]), ALL(Table1)) * .95

This will Count ALL Rows, then multiply by .95.

 

Capture.PNG

View solution in original post

10 REPLIES 10
fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

Can you give us some sample data, and even a mock-up of what you are looking for based on the data?  How do Open/Closed Pendin Status relate to each other, and are you looking for a Count or Sum of some data?

Highlighted
zpaul_010 Regular Visitor
Regular Visitor

Re: How to make a percentage target line using Line and stacked column chart?

This is the graph I have so far. Pls dont mind the target line, that's why I posted here for. Capture3.PNG

 

 

 

Capture1.PNG

 

  basically, if the request status is closed or cancelled, the pending status is set to closed. 

Capture2.PNG

 

Lastly, i'm looking for the count of all data. 

 

 

 

fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

 

How's this for a Measure?

 

5% = CALCULATE(COUNT(Table1[Status]), ALL(Table1)) * .95

This will Count ALL Rows, then multiply by .95.

 

Capture.PNG

View solution in original post

fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

P.S. If you have a timeframe (or any other Slicer / Filters) on the data, you may need to use ALLEXCEPT instead of ALL to limit the data to any sliced data...

 

5% = CALCULATE(COUNT(Table1[Status]), ALLEXCEPT(table1,Table1[CreateDate])) * .95

zpaul_010 Regular Visitor
Regular Visitor

Re: How to make a percentage target line using Line and stacked column chart?

Thanks. I applied the 5% on the Line values and it came as a straight line. 

How I envision it is, if one technician has taken 100 request, the 5% should be equal to 5, while for the other technician who completed 500 requests, his 5% should be 25.

 

Hence the target line should be relative to the technician's request count.

fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

ok, re-reading everything I understand.   Let me give it a try... FOrrest

fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

.... one more question...  "Wht's the big picture here..."...   I'm confused how a line WAY at the bottom of a stack (5%) is related here?  Are you trying to say, 5% of a tech's closed tickets are allowed to be open?  If so, I would imaging the stack with Closed and Open tickets, then the line = 105% of the closed total.  If the Open (tip) of each Stack goes ABOVE the line, they have too many open tickets compared to the 5% closed???

 

FOrrest

fhill Senior Member
Senior Member

Re: How to make a percentage target line using Line and stacked column chart?

Capture.PNG

zpaul_010 Regular Visitor
Regular Visitor

Re: How to make a percentage target line using Line and stacked column chart?

Thanks again fhill. This works for me. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 250 members 2,470 guests
Please welcome our newest community members: