Hi Alex,

Thanks for posting in the Community!

I’m happy to look into the matter with you.

So I have a full picture of what you are working with, would you mind sharing a screenshot of your entire conditional formatting rule (or a few sentences explaining it)?

Also, can you confirm whether your org chart is built from linked data?

Thanks!

Hi! Thanks for your help

Its built from sample data, im just trying to get it to work before I edit and load my actual data, here's screenshots of it all

Does this formula work for you?

=@”Total Reports” - COUNTIF(DOWNSTREAMDEEP, this.”header” = “true”)

CHILDREN gets shapes on a page or in a selected group. DOWNSTREAMDEEP gets anything that has an arrow drawn from the selected shape to it, recursively. I also saw my header field was imported lowercase even though the “true” was uppercase in the data I imported from.

It does! thank you so much!

Is it possible to expand on this, so I only count the downstream values if they have a property that isn't blank? i.e. this.”countmefield” != “”

=@”Total Reports” - COUNTIF(DOWNSTREAMDEEP, this.”header” = “true”)

Thanks for your help

Yes, that’s possible. I changed the formula to

=COUNTIF(DOWNSTREAMDEEP, AND(this."countmefield" != "", this."header" != "true"))

which resulted in this

I added the “countmefield” to one of the headers to make sure it was still excluded.

 

There is something weird about this formula that makes it work correctly. Since I included both “countmefield” and “header” in the formula as part of the “downstreamdeep” mapping, it will only consider objects that have both of those fields. So the calculation is not run on “Electrics”, “Wood Work”, or “Bill” because they all do not have the “countmefield” field. Then “2nd Fix” and “Rose” are not counted because they have “header”=”true” or the “countmefield” field is empty.

That’s why I had to switch from using @”Total Reports” - formula to strictly counting the things I wanted. It would exclude the objects I wanted to subtract from consideration, so they were not subtracted.

 

Hopefully that makes sense and is what you are looking for. Let me know if you need something different.

Thanks Lance, I added this but it doesn't seem to work? when I added two more blank “New Employees” under the Vacancy, they were counted. see below. The one I highlighted has nothing in “Work Group Description” (this is the field im using in the event its blank its not counted). but you can see Administration has 3 now when it should still have 1

Thanks

I asked the team who owns this feature for some help and found that newly created employees have NONE in that field instead of “”. So we need to adjust our formula to

Thanks Lance, this works now!

Hi @Alex Will! I’m Abby from Lucid, reaching out because I think you, and others who might come across this topic in the future, might be interested in Lucid’s Data Trusted User Group. This group is made up of knowledgeable users who regularly analyze and visualize data in their work. As part of this group, you’ll have the opportunity to connect directly with Lucid’s product team, gain early access to beta features, and share insights about your needs, shaping the future of Lucid’s data solutions 🎉 You can learn more and request to join here. Thanks!