cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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  
3 REPLIES 3
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.

 

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.
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


Capture3.JPG

Highlighted
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"

 

Capture4.JPG

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 373 members 3,783 guests
Please welcome our newest community members: