Forum Discussion

Rob123's avatar
Rob123
Frequent Visitor
1 year ago
Solved

Stacked Bar Chart Conditional Formatting

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
    Rob123
    1 year ago

    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
HarishKM's avatar
HarishKM
Super User
1 year ago

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's avatar
    Rob123
    Frequent Visitor
    1 year ago

    Hello Harish,

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

    Any other ideas?

    Many thanks,