Stacked Bar Chart Conditional Formatting

[Rob123]

Hello, I would most appreciate assitance with the below please! Many thanks!! 🙂

Background:

I have a table called SM4_BUCKETLIST (this is where all my data is stored).

There is a process I am looking to perform analysis on.

The cyclone is filled with material - the volume in the cyclone is recorded -> SM4_BUCKETLIST[STR_CYCLONEFILLVOL]. 

The cyclone then discharges the material into into a bucket and this bucket is then lifted. This cycle is repeated.

Each bucket has a bucket number which is recorded -> SM4_BUCKETLIST[L_BUCKETNR]

I have created a stacked bar chart.

I have shown the bucket number on the X axis and the SM4_BUCKETLIST[STR_CYCLONEFILLVOL] on the Y axis.

I have added constant Y axis lines to show the typical bounds of the process (see attached image):

Upper Line -> [Cyclone Vol UCL 1StdD] 

Average Line -> [Avg Cyclone Vol]

Lower Line -> [Cyclone Vol LCL 1StdD]

Measures:

[Avg Cyclone Vol] = AVERAGE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

[Std Dev Cyclone Vol] = STDEV.P(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

[Cyclone Vol UCL 1StdD] = [Avg Cyclone Vol] + [Std Dev Cyclone Vol]  <- "Upper Control Limit" 1 standard deviation above average.

[Cyclone Vol LCL 1StdD] = [Avg Cyclone Vol] - [Std Dev Cyclone Vol]  <- "Lower Control Limit" 1 standard deviation below average.

Goal:

I would like to conditionally format the stacked bar chart.

I would like the portion of the line below the line to show one colour.

I would like the portion within the LCL -> UCL bound to be another colour.

I would like the portion above the UCL to show another colour.

There is an example video here that I have followed: https://www.youtube.com/watch?v=ONbIwJi2GH8&t=662s

I have also included an image showing the desired outcome.

desired format

Issue:

I have started off by calculating the portion of the stacked line below the chart: 

Cyclone Vol Lower =

 IF([STR_CYCLONEFILLVOL] >= [Cyclone Vol LCL 1StdD], [Cyclone Vol LCL 1StdD], SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

However, when I plot [Cyclone Vol Lower] on the Y axis, it shows the exact same value as when I plot the [STR_CYCLONEFILLVOL] value on the Y axis. (I have tried aggregating on chart as min/max/sum etc)

Image showing the same result:
[STR_CYCLONEFILLVOL] = Chart with blue bars.
[Cyclone Vol Lower] = Chart with purple bars.

I believe the error could be being caused as my [Std Dev Cyclone Vol], [Cyclone Vol UCL 1StdD], [Cyclone Vol LCL 1StdD] calcs are being calculated at row level, and therefore there is no Standard deviation as it is being calculated on just one value. (However I could be wrong).

I will need to do the same for the middle section, and upper section, however I think I will be able to do this if I can get help to calculate the lower section of the bar.

Can you help me correct my DAX to enable the calculation of the [Cyclone Vol Lower] please?

Note: I have thought about "hard coding" the UCL, LCL etc as a new column in the table and doing my calcs that way, however, ideally I would like for the measures to be calculated coninually so that the averages/LCL/UCL/Std Dev can be calculated over the entire dataset as the process matures.

Many thanks,
Rob

[v-pgoloju · Accepted solution]

Hi @Rob123,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @HarishKM   for the prompt and helpful response.

Try using fallowing dax measures :

Cyclone Vol Lower =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR lcl = [Cyclone Vol LCL 1StdD]
RETURN
IF(vol < lcl, vol, lcl)

Cyclone Vol Middle =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR lcl = [Cyclone Vol LCL 1StdD]
VAR ucl = [Cyclone Vol UCL 1StdD]
RETURN
IF(vol > lcl && vol < ucl, vol - lcl,
IF(vol >= ucl, ucl - lcl, 0))

Cyclone Vol Upper =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR ucl = [Cyclone Vol UCL 1StdD]
RETURN
IF(vol > ucl, vol - ucl, 0)

Axis is SM4_BUCKETLIST[L_BUCKETNR] and in values use 3 measures.

If the issue still persists, please feel free to share more details so we can further investigate and help resolve it.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna kumar

[Rob123 · Accepted solution]

Hello!

Thank you both for your suggestions.

Initially this did not work, which led me to look further back at my other calculations.

What I realised was my average calculations of [STR_CYCLONEFILLVOL] and my Standard Deviation calculation were getting calculated on a row level rather than considering the entire data set.

Original measures:
[Avg Cyclone Vol] = AVERAGE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

[Std Dev Cyclone Vol] = STDEV.P(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

New Measures - I changed these to the following:

Avg Cyclone Vol = AVERAGEX(ALLSELECTED(SM4_BUCKETLIST),SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

Std Dev Cyclone Vol = STDEVX.P(ALLSELECTED(SM4_BUCKETLIST),SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

Once I changed these, things Prasannag your solution worked!

Prasannag your solution:
Cyclone Vol Lower =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR lcl = [Cyclone Vol LCL 1StdD]
RETURN
IF(vol < lcl, vol, lcl)

Prasannag - I noted your use of SELECTEDVALUE(), so I thought I would try this with my original measure.

My updated original measure:
Cyclone Vol Lower =
IF(SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL]) >= [Cyclone Vol LCL 1StdD], [Cyclone Vol LCL 1StdD], SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL]))

And this worked too!!

HarishKM I also tried your solution and also tried a combination of the SELECTEDVALUE() but unfortunately I could not get this solution to work. However, I am sure the error is somewhere on my part as I am still learning.

However I have a solution which is all that matters. Thank you both again for your help.

Regards,
Rob

[v-pgoloju · Accepted solution]

Hi @Rob123,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, thanks to @HarishKM   for the prompt and helpful response.

Try using fallowing dax measures :

Cyclone Vol Lower =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR lcl = [Cyclone Vol LCL 1StdD]
RETURN
IF(vol < lcl, vol, lcl)

Cyclone Vol Middle =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR lcl = [Cyclone Vol LCL 1StdD]
VAR ucl = [Cyclone Vol UCL 1StdD]
RETURN
IF(vol > lcl && vol < ucl, vol - lcl,
IF(vol >= ucl, ucl - lcl, 0))

Cyclone Vol Upper =
VAR vol = SELECTEDVALUE(SM4_BUCKETLIST[STR_CYCLONEFILLVOL])
VAR ucl = [Cyclone Vol UCL 1StdD]
RETURN
IF(vol > ucl, vol - ucl, 0)

Axis is SM4_BUCKETLIST[L_BUCKETNR] and in values use 3 measures.

If the issue still persists, please feel free to share more details so we can further investigate and help resolve it.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & Regards,
Prasanna kumar

[HarishKM]

@Rob123 Hey,
Can you try switch function instead of IF dax.

Cyclone Vol Lower =

 switch(true(),
[STR_CYCLONEFILLVOL] >= [Cyclone Vol LCL 1StdD], [Cyclone Vol LCL 1StdD],
SM4_BUCKETLIST[STR_CYCLONEFILLVOL])

try to manual(Static) limit in your chart.

Thanks

Harish M

[Rob123]

Hello Harish,

I have tried this and unfortunately the result is the same. I get the same values.

Any other ideas?

Many thanks,