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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
MPetramalo214
Helper I
Helper I

Line Chart w/ Conditional Formatting

Hi everyone,

 

I'm trying to get a line chart to conditionally format depending on whether or not actual sales are above projected sales.  If they are, the line should be green, if not, the line should be red.  The entire line, not just certain segments or markers.

 

I've been trying this method referenced in a different thread but I'm getting this result:

MPetramalo214_0-1641581230447.png

 

The other problem that I'm having with this method, is that I can't add the line for projected/goal sales.

 

Ultimately, my graph needs to look like this but with the solid line changing to red if it is currently below the projected value (dotted gray line).

MPetramalo214_3-1641581497696.png

 

Any thoughts or ideas I haven't tried would be greatly appreciated.

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @MPetramalo214 

Since in a line chart, a value field is plotted continuously, there is no segmentation for the same field.
And when the value of one field is used to draw a line chart with two fields, it will not be able to draw continuously.

vangzhengmsft_0-1641953103276.png

Then for the above case, only the above goal measure is needed, and what is not a above goal would be the below goal.

So what we need is the [Sales Actual running total] and the above goal measure, the below goal measure will no longer be needed and the line will be continuous at this point.

Note that the above goal measure is below the running total.

vangzhengmsft_1-1641954526370.png

vangzhengmsft_2-1641954596626.png

In other words, running sales totals are "below goal" and are shown in red, and above goal measure are still shown in green.

vangzhengmsft_0-1641954360723.png

Please refer to the attachment below for details. (Edit: Please download the new attachment)

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-angzheng-msft
Community Support
Community Support

Hi, @MPetramalo214 

Since in a line chart, a value field is plotted continuously, there is no segmentation for the same field.
And when the value of one field is used to draw a line chart with two fields, it will not be able to draw continuously.

vangzhengmsft_0-1641953103276.png

Then for the above case, only the above goal measure is needed, and what is not a above goal would be the below goal.

So what we need is the [Sales Actual running total] and the above goal measure, the below goal measure will no longer be needed and the line will be continuous at this point.

Note that the above goal measure is below the running total.

vangzhengmsft_1-1641954526370.png

vangzhengmsft_2-1641954596626.png

In other words, running sales totals are "below goal" and are shown in red, and above goal measure are still shown in green.

vangzhengmsft_0-1641954360723.png

Please refer to the attachment below for details. (Edit: Please download the new attachment)

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-angzheng-msft
Community Support
Community Support

Hi, @MPetramalo214 

The reason for the segmentation is that your running total measure([Sales Actual running total in Date]) has blank values.
Drag and drop the running total measure onto the line chart to see if the line is segmented.

 

Modify the measure of your running total like below:

Cumulative Total =
CALCULATE(
    SUM( 'Table'[Sales] ),
    FILTER( ALL( 'Table'[Date] ), 'Table'[Date] <= MAX( 'Table'[Date] ) )
)

You will then get a continuous running total on the line chart.

Like:

vangzhengmsft_0-1641879366398.png

Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Does that method allow you to dynamically change the line chart data colors based on a value? I've tried two different methods, one someone recommended below, and the one I referenced in the post.  Its segmented because I'm using two measures to draw the lines; but I was hoping to acheive the measures checking if on the latest date of the current data set, we're either above or below goal; and the correct measure will draw the full line; while the other line fills blank.


Its currently separating them like:

MPetramalo214_1-1641909993432.png

 

 

 

 

 

 

JaromBIDEVatDK
Helper II
Helper II

This is going to be tricky because data colors does not have a dynamic option. What you could do is have two measures. One measure creates the entire line when Today()'s total is > projected. Have that line be conditional so if Total today > projected then total else blank(). Then have another measure that calculates the line for when the line is below projected or just the opposite of the other measure. Put both of those measures as your values for the line chart and then you can set the color for one line to green and the other line to red. The red line will only show if you are above projectedon today vs green when below projected. 

Okay, so I made the measures to check if sales > projected, but the line graph is still showing it segmented.  How can I get the measure to check for the entire timeframe that we're looking at?

MPetramalo214_0-1641592439759.png


Measures:

Above Goal = 
IF([Sales Actual running total in Date] >= [Sales Goal running total in Date],
    Sheet1[Sales Actual running total in Date], BLANK())

Below Goal = 
IF([Sales Actual running total in Date] < [Sales Goal running total in Date],
    Sheet1[Sales Actual running total in Date], BLANK())

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.