Forum Discussion
how to set dynamic target line
HI Anonymous,
Your formula seems already summarized rates, so it get the wrong result(overly 0.7) when I try to aggregated them.
You can try to use the following measures to confirm if it meets your requirement:Line Color = VAR currProduct = SELECTEDVALUE ( 'Table'[Product] ) RETURN IF ( currProduct = "A", IF ( [Rate] > 0.8, "Green", "Red" ), IF ( [Rate] > 0.7, "Green", "Red" ) )Regards,
Xiaoxin Sheng
Hi Anonymous ,
I am unable to share pbix file here.
Hence, I will just share the data in table below.
Below is the Column created:
Period = TableB[Date].[Year] & "-" & FORMAT(TableB[Date].[Date],"MM")
Passed = Sum(TableB[Count])Total = Count (TableB[Count])Rate = IFERROR([Passed]/[Total],0)| Date | Case Number | Product | Count |
| 13-08-19 15:00 | CN-01084626 | 1 | |
| 12-09-19 22:00 | CN-01046298 | A | 0 |
| 14-09-19 19:19 | CN-01047910 | A | 0 |
| 17-09-19 21:26 | CN-01061919 | A | 0 |
| 03-10-19 0:00 | CN-01065379 | A | 0 |
| 02-10-19 23:30 | CN-01065606 | A | 0 |
| 21-10-19 17:09 | CN-01067180 | A | 0 |
| 24-10-19 19:25 | CN-01067182 | A | 0 |
| 01-11-19 15:40 | CN-01068207 | A | 0 |
| 21-11-19 15:30 | CN-01068360 | A | 0 |
| 21-11-19 17:30 | CN-01068817 | A | 1 |
| 03-10-19 16:30 | CN-01065209 | B | 0 |
| 02-10-19 19:30 | CN-01066605 | B | 0 |
| 03-10-19 19:00 | CN-01068236 | B | 1 |
| 02-10-19 21:30 | CN-01068653 | B | 1 |
| 03-10-19 18:00 | CN-01068710 | B | 0 |
| 03-10-19 16:00 | CN-01068711 | B | 0 |
| 03-10-19 18:00 | CN-01068744 | B | 1 |
| 04-10-19 20:00 | CN-01068747 | B | 1 |
| 09-09-19 22:30 | CN-01074272 | C | 0 |
| 06-09-19 20:00 | CN-01084895 | C | 1 |
| 07-09-19 0:00 | CN-01087782 | C | 0 |
| 09-09-19 21:00 | CN-01088179 | C | 0 |
| 09-09-19 13:30 | CN-01112091 | C | 1 |
| 10-09-19 18:00 | CN-01112163 | C | 1 |
| 06-01-20 19:00 | CN-01101438 | C | 0 |
| 15-01-20 7:00 | CN-01114026 | C | 1 |
| 07-01-20 22:00 | CN-01118900 | C | 1 |
| 08-01-20 17:00 | CN-01120659 | C | 1 |
| 08-01-20 18:00 | CN-01127818 | C | 0 |
| 13-09-19 1:30 | CN-01070767 | D | 1 |
| 11-09-19 18:20 | CN-01072158 | D | 0 |
| 10-09-19 15:00 | CN-01072285 | D | 1 |
| 10-09-19 15:00 | CN-01075260 | D | 0 |
| 09-09-19 20:30 | CN-01075356 | D | 1 |
| 10-09-19 20:00 | CN-01075425 | D | 1 |
| 10-09-19 16:00 | CN-01075561 | D | 1 |
| 10-09-19 20:30 | CN-01077787 | D | 1 |
| 10-09-19 17:20 | CN-01079007 | D | 1 |
| 09-09-19 21:00 | CN-01081676 | D | 1 |
| 09-09-19 21:00 | CN-01082568 | D | 0 |
| 10-09-19 17:00 | CN-01084007 | D | 1 |
| 10-09-19 21:00 | CN-01084628 | D | 0 |
| 11-09-19 14:00 | CN-01085063 | D | 1 |
HI Anonymous,
Your formula seems already summarized rates, so it get the wrong result(overly 0.7) when I try to aggregated them.
You can try to use the following measures to confirm if it meets your requirement:
Line Color =
VAR currProduct =
SELECTEDVALUE ( 'Table'[Product] )
RETURN
IF (
currProduct = "A",
IF ( [Rate] > 0.8, "Green", "Red" ),
IF ( [Rate] > 0.7, "Green", "Red" )
)
Regards,
Xiaoxin Sheng
Hi Anonymous ,
Thanks for your reply. It works now.
How about the target line (Constant Line)?
Is it possible to move the target line to 80% if Product = A is selected?
HI Anonymous,
Unfortunately, constant line not same as other aggregate analytic lines, it not able to configure based on measure value. So it is impossible to make a dynamic constant line based on filter selections.
Regards,
Xiaoxin Sheng
Okay, thanks for your help Anonymous !