topic Re: Dynamic formatting of line chart points in spc chart in DAX Commands and Tips

Dynamic formatting of line chart points in spc chart

DotsAndSpots — Mon, 06 Mar 2023 18:11:00 GMT

Hello,

I am attempting to construct an SPC chart in Power BI which charts Activity, Average Activity, Upper Limit (+3 stddev) and Lower limit (- 3 std dev) over time (months). For any given month, if the activity is above the upper or below the lower limit, that point on the graph needs to be a different colour. This all needs to be dynamic as the data is selected via a slicer. My issue is therefore comparing the measures (limits) with actual row values. Can anyone advise on how to achieve this ?

Many thanks

Re: Dynamic formatting of line chart points in spc chart

ichavarria — Mon, 06 Mar 2023 20:57:47 GMT

To compare the activity measure with the upper and lower limits, you can create two additional measures in your dataset for the upper and lower limits using the following DAX expressions:

Upper Limit = AVERAGE(Activity) + 3 * STDEV.P(Activity)

Lower Limit = AVERAGE(Activity) - 3 * STDEV.P(Activity)

Then, you can create a line chart visual in Power BI with months on the x-axis and activity on the y-axis. Add the Average Activity measure as a line series on the chart, and add Upper Limit and Lower Limit as additional line series.

To highlight the points above the upper limit or below the lower limit, you can use conditional formatting. Click on the visual and go to the "Visualizations" pane. Under "Data colors," select "Background color" and choose "Advanced controls." Use the following expression to define the color for the data points above the upper limit:

= IF([Activity] > [Upper Limit], "Red", "White")

Use a similar expression to define the color for data points below the lower limit:

= IF([Activity] < [Lower Limit], "Red", "White")

This will highlight any data points above or below the limits in red, while leaving the rest of the points white.

Best regards,

Isaac Chavarria

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

Re: Dynamic formatting of line chart points in spc chart

DotsAndSpots — Wed, 08 Mar 2023 10:13:48 GMT

Hi Isaac,

Thanks you, your explanation was perfect, though I did have an issue with using the activity for comparison but got around it by creating a measure.

Re: Dynamic formatting of line chart points in spc chart

ichavarria — Tue, 14 Mar 2023 01:01:58 GMT

Great to hear my solution worked for you!