Forum Discussion

letItBept_01's avatar
letItBept_01
New Member
1 year ago
Solved

The Line Chart Breaks Midway

 

 

 

(I do not read English, so I am posting a machine-translated text from Japanese.)

I am trying to change the color of a line chart midway through, and I have successfully done so.

Steps to change the color:

  • I prepared a CSV file as the data source for the line chart.
  • The CSV contains three columns: Year, Percentage, and Notes.
  • In the line chart settings, I set:
    • X-axis → Year
    • Y-axis → Percentage
    • Legend → Notes
  • Then, I customized the color and line style for each value in Notes:
    • Visualization → Format Visual → Lines → Adjust settings for each value in Notes.
  • As a result, I successfully applied colors as follows:
    • Actual → Blue
    • Forecast → Orange
    • Target → Pink

Issue:

However, as shown in the screenshot, the line breaks at the transition from "Actual" to "Forecast".
A gap appears in the middle of the chart.

Question:

How can I remove this gap and connect the line smoothly?
Is there a way to ensure continuity in the line chart?

 

---

 

Here is the original Japanese text before translation.▼

 

 

タイトル：
折れ線グラフの途中から途切れてしまう

本文：
※私は英語が読めないため、日本語を機械翻訳した文章を投稿しています。

折れ線グラフにて、途中から色を変えようとしています。
それには成功しました。

色変更の手順は以下のとおりです。
・折れ線グラフ用のデータとしてcsvを用意。
・csvでは Year Percentage Notes の３つの列を用意。
・折れ線グラフでは、「X軸にYear」「Y軸にPercentage」をセット。
・「Notes」を「凡例」にセット。
・視覚化 ＞ ビジュアルの書式設定 ＞ 行 から、「系列」で、Notesの各値ごとに色や線形をセット。

これで、Notesの「Actuarl → 青」「Forecast → オレンジ」「Target → Pink」を実現しました。

しかし、スクリーンショットの通り「ActualからForecastの変わり目」で、線が繋がりません。
途中で空白が出来てしまいます。

この空白を無くして、線をつなげたいと思っています。

この場合、線をつなげる方法はあるのでしょうか。

Need Help
Tips and Tricks
Tutorial Requests
  • MFelix's avatar
    MFelix
    1 year ago

    Hi letItBept_01 ,

     

    You line breaks because the information starts where the other stops meaning that since you do not have any data point for actual in 2025 the line ends in 2024 and does not connect to the other one.

     

    There are a couple of options here:

    Create the following measures:

    Total Value = CALCULATE(SUM('Table'[Percentage]), REMOVEFILTERS('Table'[Notes]))

Actual = CALCULATE(SUM('Table'[Percentage]) , 'Table'[Notes] = "Actual")

Forecast = CALCULATE(SUM('Table'[Percentage]), 'Table'[Notes] = "Forecast")

Target = CALCULATE(SUM('Table'[Percentage]), 'Table'[Notes]= "Target")

     

    Now add the measures to you line chart in the following order:

    1. Total Value
    2. Actual
    3. Forecast
    4. Target

    Then color you total value in a grey color

     

    Second option:

    Do not add the total measure and change the forecast and Target to the following code:

    Forecast update = VAR _MinimunYear = MINX(
			FILTER(
				ALL('Table'),
				'Table'[Notes] = "Forecast"
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) = _MinimunYear - 1,
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Year] = _MinimunYear - 1
				),
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Notes] = "Forecast"
				)
			)

Target Updated = VAR _MinimunYear = MINX(
			FILTER(
				ALL('Table'),
				'Table'[Notes] = "Target"
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) = _MinimunYear - 1,
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Year] = _MinimunYear - 1
				),
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Notes] = "Target"
				)
			)

     

    Now add the measures in the following order:

    1. Target Updated
    2. Forecast Updated
    3. Actual

    See PBIX File attach.

     

     

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    Hi letItBept_01 ,

     

    You line breaks because the information starts where the other stops meaning that since you do not have any data point for actual in 2025 the line ends in 2024 and does not connect to the other one.

     

    There are a couple of options here:

    Create the following measures:

    Total Value = CALCULATE(SUM('Table'[Percentage]), REMOVEFILTERS('Table'[Notes]))

Actual = CALCULATE(SUM('Table'[Percentage]) , 'Table'[Notes] = "Actual")

Forecast = CALCULATE(SUM('Table'[Percentage]), 'Table'[Notes] = "Forecast")

Target = CALCULATE(SUM('Table'[Percentage]), 'Table'[Notes]= "Target")

     

    Now add the measures to you line chart in the following order:

    1. Total Value
    2. Actual
    3. Forecast
    4. Target

    Then color you total value in a grey color

     

    Second option:

    Do not add the total measure and change the forecast and Target to the following code:

    Forecast update = VAR _MinimunYear = MINX(
			FILTER(
				ALL('Table'),
				'Table'[Notes] = "Forecast"
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) = _MinimunYear - 1,
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Year] = _MinimunYear - 1
				),
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Notes] = "Forecast"
				)
			)

Target Updated = VAR _MinimunYear = MINX(
			FILTER(
				ALL('Table'),
				'Table'[Notes] = "Target"
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) = _MinimunYear - 1,
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Year] = _MinimunYear - 1
				),
				CALCULATE(
					SUM('Table'[Percentage]),
					'Table'[Notes] = "Target"
				)
			)

     

    Now add the measures in the following order:

    1. Target Updated
    2. Forecast Updated
    3. Actual

    See PBIX File attach.

     

     

    • letItBept_01's avatar
      letItBept_01
      New Member
      1 year ago

      Thank you!
      It looks like the verification will take some time, but I'll give it a try.