Two questions about layout of a line chart

[BPTBPTBPT]

1.

I have this line chart (x-axis = DATE, y-axis = CountofSO). Here, I see the line running all the way to the top. I think this happens because the highest value of CountofSO is being used as the upper limit of the line. I don't want that, I want some space above the line. What is the best way to achieve this? I would like the max value of the y-axis to be 1,2 times the highest value of Countof SO.

NB. My Y-axis settings in the Range tab: Minimum = 0, Maximum = auto. 

2. Is there a way to make the last value in my line chart (on the far right, which is today's value) to stand out? Currently I am using dots on the line and also data labels. Would it be possible to give the last dot and value another color?

[PavanLalwani]

Here’s how to address each of your layout requirements for the line chart in Power BI:

1. 1. Add Space Above the Line (Y-Axis Max Value)

To add some space above the line by setting the maximum value of the Y-axis to 1.2 times the highest value of `CountofSO`, you can manually adjust the Y-axis range:

- Step 1: In the Visualizations pane, go to the Format Your Visual tab (paint roller icon).

- Step 2: Expand the Y-axis settings, and locate the Range options.

- Step 3: Set the Maximum value manually. To calculate the maximum value:

  - Find the current maximum value of `CountofSO` (you could check this by temporarily setting the Maximum to "Auto" and seeing the upper limit Power BI assigns).

  - Multiply this value by 1.2 (for example, if your maximum `CountofSO` is 100, set the Maximum value to 120).

- Step 4: Set Minimum to 0 (if it’s not already).

This should give you extra space above the line.

1. 2. Highlight the Last Value in the Line Chart

To make the last value in your line chart stand out, you’ll need to use a workaround, as Power BI doesn’t currently offer a direct feature for coloring just the last data point in a line chart. Here’s how to approach it:

 Option A: Use Conditional Formatting with a New Measure

1. Create a Measure to Identify the Last Value:

   - Go to the Modeling tab and create a new measure:

     `DAX

     LastValueCountofSO =

     IF(MAX('Table'[Date]) = TODAY(), [CountofSO], BLANK())

     `

   - This measure returns the `CountofSO` only for the last date (today’s value), leaving other dates blank.

1. Add the Measure to the Line Chart:

   - Add this new measure (`LastValueCountofSO`) to the line chart as an additional Line or Marker (depending on the chart type).

   - Go to the Format Your Visual tab, and adjust the color of the new measure to highlight it differently from the main line.

 Option B: Customize the Last Marker and Data Label

   - If adding a measure doesn’t achieve the desired effect, you can also customize the Data Label and Marker colors.

   - Go to Data Labels and set custom colors, if needed, for the last data point only.

Using these techniques, you can create a distinct visual highlight on the last point in your line chart.

If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!

[BPTBPTBPT]

Hi, 

Please see the simple PBIX I created.

https://u.pcloud.link/publink/show?code=XZSoa75Z5oYwDsyLSrRNyklHXWQI2kiwBaCy

Note that in this PBIx one can make new columns, were in my actual working report I can't (working with a live connection so I can only make measures).

So I am looking for solutions that I can use in a live connection environment.

My initial two questions are still valid:

1) how do I make the last "bullet" on the graph and the last data value red instead of blue?

2) how do I create headroom above the line? Let's say 20%? It should be adaptive because the values will change.

Thanks!

[Kedar_Pande]

@BPTBPTBPT 

To create space above your line in the line chart, you can manually set the maximum value of the Y-axis to be 1.2 times the highest value of CountofSO

Change Maximum from Auto to a fixed value. If your highest CountofSO value is, for example, 100, set the Maximum to 120 (1.2 times 100).

If you want to change just the last dot's color, you can create a measure that returns a specific color for today’s value and apply that in the Data colors settings

[BPTBPTBPT]

Thanks, but the values will change so I need a flexible solution.

On #2: I tried: 

#SOvanvandaag = CALCULATE(
SELECTEDVALUE('factSG_SOOngepland'[CountofSO]),
'SnapShotDate'[Date] = TODAY()
)

But don't know how to use it as a conditional fomatting. I can't use it as Field Value, I simply cannot select this measure. Don't know if the measure is right as well for this purpose.

won't this go wrong when several dates in my graph have the same value as today's? They will all get the notable color. I need that only for today.

[lbendlin]

to create a flexible space you can create a measure that is 1.2 the maximum value of allselected CountofSO, and then use that measure as the fx.

[BPTBPTBPT]

Can you help me with this? 

Please see my earlier comment in this thread: "I also tried creating a new calculated column, with all SO values times 1.2, and using that as fx. But I get the same result: it seems like the sum is being used, instead of max. When I try using MAX  on the calculated column, it give me an error.: [quick measure] = MAX[SO] * 1.2"

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[PavanLalwani]

### Answer to both questions:

1. **Add Space Above the Line**:
- Set the **Maximum** value of the Y-axis manually to 1.2 times the highest value of `CountofSO` by calculating the maximum value and entering it in the Y-axis settings. For example:
- Go to the **Format** pane of your chart → **Y-axis** → Set **Maximum** to a custom value like `Max(CountofSO) * 1.2`.

2. **Highlight Last Data Point**:
- Unfortunately, Power BI doesn't allow dynamic coloring for just the last point. A workaround:
- Create a **calculated column** to identify the last value and use **conditional formatting** to change the color of the last dot and data label.

These will give you the desired visual results!

[BPTBPTBPT]

On #2: could you help me a little bit more? I can create a quick measure, but how do I indicate the "last" value of the SO field?

[BPTBPTBPT]

Hi, @PavanLalwani 

Thank you for your answer. 

On #1:

When I use the fx option for the maximum range of the Y-axis, I don't know where to enter dsuch a formula as you mentioned. I can enter only decimals in the box.

When I click fx (maybe you mean that one), I can enter:

- Format style (which gives me just one option:"Field value"),

- What field should we base this on? Here I can pick the SO field (which is a measure, indicated by a calculator icon next to it). But. I think this gives me the sum of all SO's, the graph then gets really high. I don't see anywhere to put the "times 1.2" in.... 

I also tried creating a new calculated column, with all SO values times 1.2, and using that as fx. But I get the same result: it seems like the sum is being used, instead of max. When I try using MAX  on the calculated column, it give me an error.: [quick measure] = MAX[SO] * 1.2

[lbendlin]

some of this may be possible. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[BPTBPTBPT]

Hi, 

Please see the simple PBIX I created.

https://drive.google.com/drive/folders/1NpmgeMXsinLFh9QR8DllN3hGW_qjXxUx?usp=sharing

Note that in this PBIx one can make new columns, were in my actual working report I can't (working with a live connection so I can only make measures).

So I am looking for solutions that I can use in a live connection environment.

My initial two questions are still valid:

1) how do I make the last "bullet" on the graph and the last data value red instead of blue?

2) how do I create headroom above the line? Let's say 20%? It should be adaptive because the values will change.

Thanks!

[lbendlin]

use the measure for the max y range.

[BPTBPTBPT]

Thanks! 

I tried it but it does not work for me. 

when I change the value from 1.2 to 1.5 for example, the expected outcome would be that there would be even more "room" above the main line in the chart. Instead, the main line ('Datumenwaarde'[Waarde]) gets different values (?!)

See screenshots here where I only change the multiplication factor of the measure.

[lbendlin]

"Waarde", RANDBETWEEN(0, 20)  -- Voeg willekeurige waarden tussen 0 en 20 toe

[BPTBPTBPT]

Hi, I had a slight break but diving into this again.

I tried copying the measure you had made:

Last Point =

var md = maxx(all(DatumEnWaarden[Date]),[Date])

return if(max(DatumEnWaarden[Date])=md,sum(DatumEnWaarden[Waarde]))

I am assuming you are using the X and Y axes, so:

Last Point =

var md = maxx(all(X),X)

return if(max(X)=md,sum(Y))

Which becomes:

Last Point =

var md = maxx(all('SnapShotDate'[Date]),[Date])

return if(max('SnapShotDate'[Date])=md,sum('factSG_SOOngepland'[CountofSO]))

in my final sheet.

I created this measure and added it to my visual:

However, when I REMOVE the #SO line, I would expect to only see the last point. Instead, it shows nothing, just an empty graph.

When I add #SO back again, I cannot distinguish between two lines when trying to adjust the color, as Power BI only "sees" one line:

Here I had expected to be able to choose between #SO and Last Point. I cannot choose though.

What am I doing wrong here?

EDIT..

Pff, I put a lot of time into this,. tried numerous alternative measures using chatgpt but nothing works. I hope you can help me. Maybe there is some verification I can do to see if the measure works?

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[BPTBPTBPT]

I would like to, but how? What do you need?

I am working with a secured live connection, so I cannot provide or hand over the data. Would screenshots with example data from 'SnapShotDate'[Date] (x-axis) and 'SnapShotDate'[Date] (Y) make things more clear? If so, should I use any filters?

[lbendlin]

Unfortunately I cannot assist you if you are unable to provide sample data.  I cannot help with Live Connection scenarios. I hope someone else can help you further.

[BPTBPTBPT]

Thank you, I think this ight be my solution(s). I will try to get it into my actual report.