I m trying to create a line chart with a shaded target area using the Error Bands/Bars, but the target area keeps coming out like a trapezoid, not a rectangle. Sample data is below (data_sample). I bring in the data using DirectQuery.
I believe part of the issue is that the x values are not small/refined enough to make a rectangle. I tried making a different table with GENERATESERIES and bringing in the Target value (upper bound) but I was not successful.
For the raw data, I used the "Line Chart" visualization with the "Error Bars/Bands" enabled, and I get below.
What I want is something like this (Excel):
One wrinkle is that the line for Value2 is "dynamically" colored based on a measure called m_score. The idea is that if the score fits as defined below, then a green colored line appears and a red one disappears (and vice versa).
m_Green_line = IF([m_score]>=7.5, [m_value2], BLANK())
m_Red_line = IF([m_score]<7.5, [m_value2], BLANK())
m_target = MAXX(data_sample,[Target]+0)// upper bound for error bars
m_lower = 0 // lower bound for error bars
I also tried using the "Line and stacked column" visualization, which looks almost right, but I cannot get the shaded area to be transparent because there is no fx or transparency option to color it with a measure. I want to be able to see the dotted grid lines.
Is there a way to accomplish what I want using the "Line Chart" Visualization? Or by fixing the "Line and stack column chart"? Also, I would prefer the x-axis to be Continuous not Categorical, but I understand if that is not possible.,
data_sample
| Percentiles | Value1 | Value2 | Target |
|---|---|---|---|
| 10 | 0 | 0 | |
| 25 | 0 | 0 | |
| 50 | 1 | 0 | |
| 75 | 7 | 2 | |
| 90 | 33 | 11 | |
| 95 | 93 | 36 | |
| 98 | 259 | 121 | |
| 99 | 462 | 273 | 1400 |
| 99.2 | 516 | 335 | 1400 |
| 99.4 | 692 | 418 | 1400 |
| 99.6 | 821 | 524 | 1400 |
| 99.8 | 1136 | 851 | |
| 99.9 | 1315 | 1085 | |
| 99.99 | 1932 | 1545 |