Circular Reference - creating measures using calculated column or measure

[Anonymous]

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

[v-yulgu-msft]

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

[Anonymous]

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

[Anonymous]

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"