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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
user01
Helper I
Helper I

Fixing line chart shaded target area from trapezoid to rectangle

I'm trying to make a target area for my data, but it's coming out as a trapezoid and I need it to be rectangular. I get the data via DirectQuery (sample below).

 

I have 4 main measures:

 

m_Greenline = IF([m_Score]>=7.5, [m_Value2], BLANK())
m_Lower = 0
m_Redline = IF([m_Score]<7.5, [m_Value2], BLANK())
m_Target = MAXX(data_sample, [Target]+0)
m_Value2 = SUMX(data_sample,[Value2])

 

m_Score is another measure in a different table, but it's hard-coded right now (m_Score = 5). Based on the score, one of the lines disappears and the other appears, and they are colored red or green as above (trying to make a dynamically colored line). I want to add to this line chart a target area using Error Bars/Bands, but I get a trapezoid. I want a recantgle with the width in [99, 99.6] and height = m_Target.

 

I was able to accomplish something close with the Line and Stack Column Chart, but it's with the x-axis as "Categorical". I would like to use x-axis "Continuous". Is this possible with Line Chart (or some other visualization)?

 

This is what I get:

 

trap.PNG

 

 

 

 

 

 

 

 

 

I want something like this but "Continuous":

rect.PNG

 

 

 

 

 

 

 

 

 

 

 

data_sample:

PercentilesValue1Value2Target
1000 
2500 
5010 
7572 
903311 
959336 
98259121 
994622731,400
99.25163351,400
99.46924181,400
99.68215241,400
99.81,136851 
99.91,3151,085 
99.991,932891 

 

 

1 ACCEPTED SOLUTION
user01
Helper I
Helper I

I solved my issue. It looks like part of my issue was strictly setting the lower bound to 0. The lower bound measure (m_lower) should

  • be BLANK whenever the Target is BLANK, and
  • be 0 where the Target is defined.

The upper bound measure should simply return whatever Target is for the Percentile (BLANK or numerical value).

I believe that idea works. I don't have an exact solution/measure as I ended up going in a slightly different direction.

View solution in original post

5 REPLIES 5
user01
Helper I
Helper I

I solved my issue. It looks like part of my issue was strictly setting the lower bound to 0. The lower bound measure (m_lower) should

  • be BLANK whenever the Target is BLANK, and
  • be 0 where the Target is defined.

The upper bound measure should simply return whatever Target is for the Percentile (BLANK or numerical value).

I believe that idea works. I don't have an exact solution/measure as I ended up going in a slightly different direction.

P.S. There could be other solutions. I think creating a new column with the values I described for m_lower should work as the lower bound too. In my case, I think I created a new column in my query with the lower bound described in my answer and I believe it worked too.


PercentilesValue1Value2TargetLower
1000  
2500  
5010  
7572  
903311  
959336  
98259121  
994622731,4000
995163351,4000
996924181,4000
1008215241,4000
1001,136851  
1001,3151,085  
1001,932891  

Not sure what happened to the decimals.

PercentilesValue1Value2TargetLower
1000  
2500  
5010  
7572  
903311  
959336  
98259121  
994622731,4000
99.25163351,4000
99.46924181,4000
99.68215241,4000
99.81,136851  
99.91,3151,085  
99.991,932891  
parry2k
Super User
Super User

@user01 check this video on my YT channel and see if it helps https://youtu.be/Y9Px9ZIjl6k



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.