Circular dependency workaround for bar chart legends

[cmitchell]

Sample Data CSV link

Hello, I've been banging my head against the wall for a while now trying to figure out a workaround for a circular dependency issue I'm having. The problem is this: I have a large table with 200+ columns, some calculated, some queried from the source (Excel Spreadsheet). I've created three measures to calculate quarterly, annual, and all-time supplier scores (average of factory scores associated with each supplier over the respective time duration). Now I want to plot these measures in 3 different bar charts with a discrete, quintile grade scale in the legend ranging from A+ to D. 

All supplier scores should have a single grade associated with them, and this grade should change as the supplier score does. This means that when specific factory locations for a single supplier are sliced, the supplier score AND grade should change. I also need to be able to slice the measure values by grade. I'm able to do this with a nested-IF function in a calculated column (formula below), but I can only refer to one of the three measures within a single data table before getting a circular dependency error. In this formula Supplier Score by Quarter is a measure.

Supplier Grade by Quarter = IF([Supplier Score by Quarter]<50,"A+",IF([Supplier Score by Quarter]<100,"A",IF([Supplier Score by Quarter]<150,"B",IF([Supplier Score by Quarter]<200,"C","D")))) 

Here is my relationships view showing how the queries are linked. FA, IPM, and PSI are service types. 

Relationships Pane

I understand why this error is occuring as explained here, but I'm hoping to find a work-around that doesn't require as much memory as duplicating the 200+ column data table for each measure-based grade column

Thanks in advance for your help!

[Greg_Deckler]

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