Month after month, Pigmenauts share challenges, ideas, and solutions that help us all learn and grow together.

To celebrate this knowledge sharing, here are October’s top 5 questions. We’re highlighting the ones that sparked the most interest and views from fellow members 💡.

Whether you faced the same challenge or want to learn new tips, these threads are a great place to explore, contribute, and connect.

1. Conditionnal Formatting based on the hierarchy level

Question - @Sandy B:

“I’d like to apply conditional formatting dynamically, based on the level of granularity in my City → Department → Region → Country hierarchy. How can I calculate the level of granularity for formatting?”

Best Answer - ​@Laszlo:
You don’t need a separate metric!
Use the Tabular layout to format by level:

1. Format your top level and select Apply to: Every instance of my selection.

2. Repeat for each level down.

3. Switch back to Tree view in the Layout menu.

Read the full discussion here:

2. Using Calculated Items for aggregating more than 2 list items

Question - ​@Rushi:

“I’m using Calculated Items to sum list values like Product M Series or Product S Series, but I can’t aggregate more than two items. Any tips?”

Best Answer - ​@Min Li:

Calculated items can’t handle three or more list items directly. You can create separate metrics for each group, though it can get redundant. It’s a limitation to be aware of when working with multiple aggregations.

Read the full discussion here:

3. Reset cumulation on blank?

Question - ​@ljanowski:

“I’m cumulating months of employment per vendor, but need it to reset when there’s a gap (blank value). How can I do that?”

Best Answer - ​@Laszlo:

Use the PREVIOUS() function combined with an IF statement instead of CUMULATE():

IF ( ISDEFINED ('Vendor Employed'), PREVIOUS(Month)+'Vendor Employed', BLANK)

Read the full discussion here:

4. Return per employee the average salary of its departement

Question - ​@Elouan:

“How can I show each employee the average salary of their department?”

Best Answer - ​@Tobias Gorsia:

• Create a metric for Avg Salary by Department:
  AVERAGE(Salary) BY Department

• Reference it in your employee metric:
  [Avg Salary by Department] BY Department
  This returns the same average value for all employees in the same department.
  Check your dimensions if results aren’t unique, misalignment between metrics is a common culprit!

Read the full discussion here:

5. Need major help staging up a lease schedule

Question - ​@r.karsan:

“I’m new to Pigment and struggling to structure a lease schedule that includes default regional values, manual inputs, and monthly amortization.”

Best Answer - ​@Laszlo:

You can achieve this by creating multiple input metrics dimensioned by Lease Selector. One for each input field such as region, lease value, cage fee, rent escalator, term, and start date.
Use formulas to prepopulate fields with default values from the L1 Geo dimension (for example, 'L1 Geo'.'Default Lease Value'[BY: Input_Region]) and allow overrides where needed. Finally, combine all input metrics into a single table for a clear, structured setup.

Read the full discussion here:

These questions reflect the curiosity and creativity of the Pigment Community, and your insights can make them even more valuable. If you’ve faced similar challenges, drop your ideas, solutions, or tips in the threads.

Have your own question? Start a new thread and let the community help!