Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
zpaul_010
Helper I
Helper I

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

 

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

10 REPLIES 10
fhill
Resident Rockstar
Resident Rockstar

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?




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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. 

 

 

 

Anonymous
Not applicable

My problem is that I want to show the target number only in the last month column.

 

How can I do this?

 

 

 

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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.

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




fhill
Resident Rockstar
Resident Rockstar

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Thanks again fhill. This works for me. 

fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.