Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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  
3 REPLIES 3
v-yulgu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

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


Capture3.JPG

Anonymous
Not applicable

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"

 

Capture4.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.