I think I’ve got a working solution for you. My one remaining question is how you want to handle blanks as opposed zeros??
To deal with the sort issue, I created a custom sort field in power query using the first digits after the "("and then just sorted the Year and Month column with that.
In terms of the custom formatting, I used a measure very similar to the one I proposed above. The main difference is that I remembered a trick from @heather that I liked a lot and incorporated that into these measures. Her trick was to set a color value in its own measure, and then just reference that measure in all the other conditional formatting measures. That way, if you decide to change the color of the highlighting, you have one change to make rather than a dozen.
CF Background 105 =
IF(
VALUE(SELECTEDVALUE('Table'[202105] )) > 0,
[CF Color],
BLANK()
)
I hope this is helpful. Full solution file attached.
– Brian
eDNA Forum - Waterfall Report Solution.pbix (84.7 KB)