Line Chart - With States or Change color with direction of trend

[shivkonar]

Hello,

I am trying to create line chart with changing color - green(up) when the current data point value is greater than previous data point and red(down) 

 

I have created the following measures in DAX:

1. [Total Sales]

2. [PM Sale] = 
CALCULATE([TotalSales], PARALLELPERIOD('Date'[Date], -1, MONTH)) // Previous month Sales

3. [Indicator] = IF([TotalSales] - [PM Sale] >=0, 1, 0)

4. [IndicatorText] = IF([TotalSales] - [PM Sale] >=0, "UP", "DOWN")

I thought adding the [Indicator] or [IndicatorText] to "Legend" property of line chart would be possible and then be able to change the color, but it is not.

Any ideas?

Thanks, Shiv 

[v-danhe-msft · Accepted solution]

Hi @shivkonar,

Based on my test, you can refer to below steps:

1.I have entered some sample data like the picture below and the column[a] and column[Legend] is calculated column:

a = var a=[Month]+1

return CALCULATE(SUM('Row Table'[TotalSales]),FILTER('Row Table','Row Table'[Month]=a))

Legend = IF([a]-[TotalSales]>=0,"A","B")

2.Create a new table and add a related column.

New table:

Table with change value = CALCULATETABLE('Row Table','Row Table'[Month]=3)

Calculated column:

ColumnA = IF('Table with change value'[Legend]="B","A")

3.Create a new middle table and add a column

New table:

Middle Table = 'Row Table'

Calculated column:

Column 2 = 'Middle Table'[Legend]

4.Union the ‘Middle’ table and the ‘Table with change value’ table.

Unio Table = UNION('Table with change value','Middle Table')

5.Create a Line chart and add the related fields, now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/gkpr4o12yokdrap/Line%20Chart%20-%20With%20States%20or%20Change%20color%20w...

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @shivkonar,

Based on my test, you can refer to below steps:

1.I have entered some sample data like the picture below and the column[a] and column[Legend] is calculated column:

a = var a=[Month]+1

return CALCULATE(SUM('Row Table'[TotalSales]),FILTER('Row Table','Row Table'[Month]=a))

Legend = IF([a]-[TotalSales]>=0,"A","B")

2.Create a new table and add a related column.

New table:

Table with change value = CALCULATETABLE('Row Table','Row Table'[Month]=3)

Calculated column:

ColumnA = IF('Table with change value'[Legend]="B","A")

3.Create a new middle table and add a column

New table:

Middle Table = 'Row Table'

Calculated column:

Column 2 = 'Middle Table'[Legend]

4.Union the ‘Middle’ table and the ‘Table with change value’ table.

Unio Table = UNION('Table with change value','Middle Table')

5.Create a Line chart and add the related fields, now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/gkpr4o12yokdrap/Line%20Chart%20-%20With%20States%20or%20Change%20color%20w...

Regards,

Daniel He

[shivkonar · Accepted solution]

Hello @v-danhe-msft,

Thank you for your solution. Please see another solution from Stack Overflow community

https://stackoverflow.com/questions/51579341/power-bi-line-chart-with-changing-color-for-trend

[v-danhe-msft]

Hi @shivkonar,

Thanks for your sharing, could you please mark my helpful replies as Answered?

Regards,

Daniel He