Conditional formatting issues

[Anonymous]

I have what seems like a fairly simple issue to solve, but can't quite get it. The below Excel screen shot shows what I'm trying to build in Power BI. Basically I have daily, MTD, and YTD percentage columns which need to be compared to a target %, and turn red if they're below target or green if above. 

I figured the X's and Check marks probably can't be done, and that's fine, but I'd at least like to have a color indicator. Based on another forum thread I created a couple calulated columns which look like this:

oe_wo_daily_compare = IF (oe_summary_report[oe_wo_ext_pct] < oe_summary_report[oe_target_wo_ext_pct], 0, 1)

and

oe_wo_mtd_compare = IF (oe_summary_report[oe_wo_ext_pct_mtd] < oe_summary_report[oe_target_wo_ext_pct], 0, 1)

However, if I try to create the YTD column using the same syntax, I get a circular reference error which I don't understand:

oe_wo_ytd_compare = IF (oe_summary_report[oe_wo_ext_pct_ytd] < oe_summary_report[oe_target_wo_ext_pct], 0, 1)

"A circular dependency was detected: oe_summary_report[column], oe_summary_report[oe_wo_mtd_compare],  oe_summary_report[column]"

I have no idea what the conflict is, or why the mtd field is included in the error message, when it isn't part of the colunm's defintion anyway. Does anyone know why this is happening, or how I can get YTD to work?

That's just my first issue - here is the 2nd. The basic concept for daily and mtd seems to work, as shown below:

So if I can get YTD to work then this is all ok, but I have two other issues:

1. Can I get rid of the 0 and 8 totals in the color columns, but leave the totals in the others?

2. If I use a data hierarchy, I can't get the conditional formatting to work for every row. This is what it looks like when I use the region Hierarchy:

Is it possible to have the conditional formatting work on the aggregate rows as well as the individual data rows?

The Power BI file can be found here.

Thank you!

[Greg_Deckler]

You should be able to do colored indicators and even checkmarks and x's now that you can set a measure to be an Image. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/SVG-Multi-Shape-Multi-Color-Indicators/m-p/5...

Circular dependencies are notoriously tough to track down. Can you provide some example/sample source data and your calculations? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@Greg_Deckler thanks for the quick reply! I'll read the link you posted now.

As for the circular reference, I included the measures in question in my post, as well as the .pbix file, thinking that would make it easy to investigate. Is there more that's needed?

thanks!

Alex