Forum Discussion

mundy727's avatar
mundy727
Frequent Visitor
2 years ago
Solved

Conditional Formatting in Matrix Visual - Custom Thresholds for Heat Mapping

Hi, im realtively new to the forums here so apologies if i dont have this posted in the correct location!   I am looking to build out a head map for something similar to the table below. Im attempt...
General Comment
Need Help
Show and Tell
  • NaveenGandhi's avatar
    NaveenGandhi
    2 years ago

    mundy727 

    Create these two measures and make the CF as the field value for conditional formatting.

    Count# = COUNT('Table'[Status])
    CF =

    Var Row_ = CALCULATE(min(Capacity[Capacity]),Capacity[Name]=SELECTEDVALUE('Table'[Name]))

    RETURN SWITCH(TRUE(),[Count#]>Row_,"Red",[Count#]<Row_,"Green",[Count#]=Row_,"Yellow")

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!!


NaveenGandhi's avatar
NaveenGandhi
Memorable Member
2 years ago

mundy727 

Thanks for the data and screenshot. Below PBIX has a solution for this, Basically all you have to do is have a capacity table to store the capacity for each recuirter. Then create measure count# and CF. 

use the CF in the conditional formatting using field values. Feel free to tweak the CF formula and color codings.

 

 

 


Let me know if you have any questions.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!


mundy727's avatar
mundy727
Frequent Visitor
2 years ago

Thanks NaveenGandhi  this is helpful! What does the CF field represented in the above screenshot 'field we should base this on'?

 

Assuming id need to build that capacity table out and then build a relationship between this table and the data source of the table with the recruiter name and the Early, advanced, offer data correct?

 

Appreciate your guidance here thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi mundy727 ,

     

    CF is the rule of color display and it needs to be built into your capacity. As per your original screenshot, you need to use CF for the "Value" in Early,Advanced, At offer. That's the field you let into the matrix to create conditional formatting for "Value", and this rule is CF.

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

     

    • mundy727's avatar
      mundy727
      Frequent Visitor
      2 years ago

      Anonymous thank you!

       

      I've built the capacity table to show the capacity of the recruiter as follows below. In effort to simplify i thought to just have the total capacity # in this table.  When i attempt to bring this field into the conditional formatting prompt 'what field should we base this on' i recieve a red box when selecting the total field from this table as shown below. Maybe i am missing a step here? Thank you again for all of your support!

       

       

      • NaveenGandhi's avatar
        NaveenGandhi
        Memorable Member
        2 years ago

        mundy727 

        Create these two measures and make the CF as the field value for conditional formatting.

        Count# = COUNT('Table'[Status])
        CF =

        Var Row_ = CALCULATE(min(Capacity[Capacity]),Capacity[Name]=SELECTEDVALUE('Table'[Name]))

        RETURN SWITCH(TRUE(),[Count#]>Row_,"Red",[Count#]<Row_,"Green",[Count#]=Row_,"Yellow")

        If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

        Follow me on LinkedIn!!!