Showing results for 
Search instead for 
Did you mean: 
azjamkat Frequent Visitor
Frequent Visitor

Circular Reference - creating measures using calculated column or measure

I have a data table like the below. I added the columns and measures described below and they work perfectly for a single month but I can't figure out how to do the same for a Quarter. My Original data does not contain quarter columns.  The easy way would be to add them in the original data but there has to be a way in Power BI.


At first, I was getting warnings about circular references but when I finally found a way to get rid of that, the numbers it returned in my report were very large. I have tried various combinations of Calculated Columns and Measures but can't seem to find the right method.


1. Measure - Divide the Jan column by 1,000 to get it in Thousands.

2. Calculated Column - Pulls the Labor out of measure in step 1 and applies a run-rate to determine a full month of actual labor

3. Measure - Sums the data in the calculated column created in step 2

4. Measure - Adds the Measure from step 3 and the Non-Labor items (non-labor also has a measure to sum it)



Unique IDCost TypeJan DollarsFeb DollarsMar Dollars
54495Labor           25,678  
54495Material           30,500  
15786Labor         264,887  
24689Labor         136,210  
246893rd Party           68,430  
v-yulgu-msft Super Contributor
Super Contributor

Re: Circular Reference - creating measures using calculated column or measure

Hi @azjamkat,


From current description, I am confused about your problem. What were you trying to achieve via above 4 steps?


Please post sample data of original data table and show us the desired output with an image.



Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
azjamkat Frequent Visitor
Frequent Visitor

Re: Circular Reference - creating measures using calculated column or measure

I have deciced that the problem is that my data is in pivoted format. I am working on re-loading my data and using Query editor to unpivot before creating measures/columns.


So far everything seems to work much better but I still need to figure out how to do one thing. In my report I have 2 tabs - one is going to show a Month view and the other a quarter view. I want to take the MTD or QTD Labor cost, calculate a "run-rate" and use that to estimate a full month or quarter of labor costs (using the company calendar for working days each month). 


In my formula, I was determining the current week #, then looking that Week # up in the table below. The formula divides the labor by the number in the WorkingDaysWeekCM column and then multiplies that number by the WorkingDaysMonthCM column. That works perfectly but I tried creating the same formula for a quarter and I get the circular reference error. On the quarter tab, I would need the formula to divide by WorkingDaysWeekCQ and multiply by WorkingDaysMonthCQ.


How do I let the filters on the report tabs determine which columns to use in the formula? 


If I am in Week 2 and I have $200 actuals so far, the two formulas would be as follows:

CM formula = (200/3.5)*18.5

CQ formula = (200/3.5)*63.5


azjamkat Frequent Visitor
Frequent Visitor

Re: Circular Reference - creating measures using calculated column or measure

Forgot to mention.....I also have the date table below:

I'll use a date filter on the first tab to select "Jan" and a Quarter filter on the secnd tab to select "Q1"



Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 429 members 4,293 guests
Please welcome our newest community members: