Circular Dependency within Table for calculated column

[Anonymous]

Hi there,

I've been tyring to correct a circular dependency between two calculated columns and I can't seem to find the answer.

I have one table that I am using for the calculations, there are no links between other tables/datasets.

I have sales and margin data for a large set of products. The products have various levels of detail to them. For example, Product "XYZ" is from Product Family 07, from Product Class 02, and from Product Line 08 with Division 4 for year 2018. I am trying to label these materials/families/classes/lines as having high sales or low sales and if they have high margin or low margin.

I want to group them based on their sales and margin. For example, IF(Product Class02 Sales > Median(Product Class Sales) AND (Product Class02 Margin) > Average(Product Class Margin) then "High Sales High Margin". I've made one calculated column that groups all the product lines with a division correctly into high/low sales and high/low margin. I want to make the same exact formula and calculated column for grouping the classes within a product line. However, I run into a circular dependency even though all the measurements within the two calculated columns do not directly depend on one another.

I need to have the second calculated column as a column and not a measure because I need to use it as part of an axis in a visualization. The measure itself works fine though and does not give me a circular dependency error. 

Example formulas:

GroupizationPL = IF(AND([MarginPL] >= [MarginDivision],[NetBillingPL] >= [MedianPL]),"Group1",IF(AND([MarginPL] < [MarginDivision],[NetBillingPL] >= [MedianPL]),"Group2",IF(AND([MarginPL] >= [MarginDivision],[NetBillingPL] < [MedianPL]),"Group3", IF(AND([MarginPL] < [MarginDivision],[NetBillingPL] < [MedianPL]),"Group4","Other"))))

*This works and properly groups the product lines within divisions*

GroupizationClass = IF(AND([MarginClass] >= [MarginPL],[NetBillingClass] >= [MedianClass]),"1",IF(AND([MarginClass] < [MarginPL],[NetBillingClass] >= [MedianClass]),"2",IF(AND([MarginClass] >= [MarginPL],[NetBillingClass] < [MedianClass]),"3", IF(AND([MarginClass] < [MarginPL],[NetBillingClass] < [MedianClass]),"4","Other"))))

*This gives me the circular dependency error. Yet when I put this into a measure it properly groups the classes within product lines*

[Chihiro]

Couple of issues here.

1. Measure is evaluated in filter context, but you are trying to use it in row context calculation.

2. You can't use measure that reference table itself in the calculated column in same table.

There are couple of ways to work around it, that I can think of.

1. Create summarized table using dax. Create relationship, perform your calculation based on it.

2. Add your calculated column(s) using "M" in query editor.

I prefer method 2. Especially when calculated column(s) are in Fact table.

[Anonymous]

How do I add my calculated columns?

[Chihiro]

Without sample data, hard to help you. I'd recommend uploading pbix with sample model (using dummy data, but with same structure) to OneDrive or some other service and share a link here.

It makes it so much easier to help you, when we don't have to recreate/imagine the model you are working with.

[Anonymous]

Any idea on how to solve this?

[Chihiro]

Try using it as measure instead of calculated column.

With your sample it returns Group1 for all but the last row (which shows Other as MarginClass & MarginPL show NaN).

Not sure if this is correct result or not.

[Anonymous]

I can't use measures since I need to use these groups as a visualization axis. 

[Chihiro]

Normally, I'd do bucketing and categorizing in Query Editor stage then.

I'll have to go through your file more in detail to follow all the steps and criteria applied. Will see if I have time tomorrow or later in the week.

[Anonymous]

Here's the simplified version of the data:

https://drive.google.com/open?id=1gvm0dxSIkp3iDj9AdyKLGSTY8vTSi8VX