Forum Discussion
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 ID | Cost Type | Jan Dollars | Feb Dollars | Mar Dollars |
| 54495 | Labor | 25,678 | ||
| 54495 | Material | 30,500 | ||
| 15786 | Labor | 264,887 | ||
| 24689 | Labor | 136,210 | ||
| 24689 | 3rd Party | 68,430 |
3 Replies
Hi Anonymous,
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.
Regards,
Yuliana Gu
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
