Forum Discussion
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 attempting to build out a recruiter capacity model. Id like to have the individual names shown here have seperate thresholds for conditional formatting (i.e. Name 1's overall capacity = 15 so id like to show the total and columns shown here be highlighted in green (not near the capacity), amber (getting closer to capacity), and then red (at or over capacity #). The heat mapping/conditional formatting would be different by name, as each individual has their own capacity. The counts shown in the example below are counts of how many roles the recruiter currently has aligned to them.
Curious if there is a way i can achieve this goal - to have seperate count thresholds for individuals (name 1, name 2, name 3) and have conditional formatting based off of these count thresholds if that makes sense?
Happy to try and explain further if any questions here. Appreciate any guidance, thank you!
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!!!
7 Replies
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!!!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!
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi mundy727
Can you share sample file(as the data model will be important to come up with a solution) and also providea screenshot of expected output to understand this better.
Regards,
NGHi NaveenGandhi sure thing. Sorry, i am unsure how to attach an excel file here - but here is an example below
Dataset exampleConditional formatting
