• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerPivot Measure with Circular Reference error

Pablo

Pablo

New Member
Hello,
I am creating a PowerPivot report for supplies consumption, which contains:
Beginning Inventory
Forecasted Demand
Actual Demand
Plan Receipt
and the measure is:
[Projected on Hand] = If([Actual Demand]>0, [Beginning Inventory] + [Plan Receipt]
- [Actual Demand], [Plan Receipt] - [Forecasted Demand] + [Prior Period Projected on Hand])
The problem is getting the [Prior Period Projected on Hand], which gives me a circular reference when used on Projected on Hand, since basically it's the same calculation. How do I get this to work?
The period used is weekly on the columns.
Thanks,
Pablo
 
Hi, Pablo!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Hi SirJB7,
Thanks for your reply. Here is the file. I made a sample file with all the info for 4 items. When the actual is positive the formula works, but when it's 0 or negative I need to add the prior period Projected on Hand, which in regular Excel is very simple, see the report tab, but with PowerPivot (or with structured tables) I get a circular reference error. The issues starts on Nov 15. Any help will be appreciated.
Thanks again,
Pablo
 

Attachments

  • Supply Consumption.xlsx
    508.8 KB · Views: 3
Hi, Pablo!

Are you very hurried? If so please take a break and dedicate 5 minute to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, 19 hours and a while... I beg your pardon and I apologize for the extremely long awaiting period... it almost looks like a geologic era...
You always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?
 
Hi Pablo ,

Can you explain your workbook in a little more detail ?

1. You have 4 tabs labelled Beginning Inv PP , Actual Demand PP , Forecasted Demand PP and Planned Receipt PP ; can you say where the tables in these tabs get their data from ? Are you manually entering all of this data or is it being imported from an external source ?

2. Is your data on the Report tab being manually entered ? Is this data being taken from the 4 tables in the tabs mentioned in 1 above , or is this data being taken from the pivot table in the tab labelled Pivot ?

3. I have not understood your problem ; is it to do specifically with Powerpivot ? If so , I do not have Powerpivot and cannot help ; if it is an Excel problem , please explain where you are facing a problem , if possible with cell addresses.

Narayan
 
Hi Narayan,
Thanks for getting back. Here are the answers:
1. Each one of the 4 tabs is a data table and each get populated by different people across the country. The files are saved in Sharepoint and then I collect the data in one workbook and put the report together (see Report tab). In regular Excel the "Projected on Hand" calculation is quite simple, but when I tried PowerPivot I got a circular reference error and since I am new to PowerPivot, I can't figure out how to solve it.
By the way, I just finished Chandoo's PowerPivot class and I thought this report would be a good candidate for PowerPivot, being the main reason the easy way to connect to the source info and refresh it (done weekly), which is better than copying and paste the new info.

2. The data in the report is a lot of vlookups to the data tables, but I copy / paste values to just focus on the pivot and have a comparison point to what I should get. I have thousands of items, the file is just a sample for 4, but it's complete for those. I also removed items descriptions and other non-essential info to make the file smaller.

3. The problem is with PowerPivot, the report in Excel works fine. On the pivot tab it works until Nov 8 (as long as the Actual Demand is positive), but starting with Nov 15 the results are wrong (when the Actual Demand is zero). For positive Actual Demand the formula is: [Beginning Inventory] + [Plan Receipt] - [Actual Demand], which works just fine, but when the Actual Demand is zero the formula changes to: [Plan Receipt] - [Forecasted Demand] + [Prior Period Projected on Hand].
[Projected on Hand] = If([Actual Demand]>0, [Beginning Inventory] + [Plan Receipt]
- [Actual Demand], [Plan Receipt] - [Forecasted Demand] + [Prior Period Projected on Hand])

The issue is getting the [Prior Period Projected on Hand], in regular Excel is simple, but in PowerPivot it generates a circular reference since the prior period (week) Projected on Hand is:
[Prior Period Projected on Hand] = Calculate([Projected on Hand], Dateadd(Calendar[Date], -7, Day))

The file doesn't have that measure, because PowerPivot doesn't even let me enter it, PowerPivot gives me the circular reference error. So what I tried was to recalculate the Projected on Hand for the prior period with this measure instead:
[Prior Proj] = CALCULATE([PlanReceipt]-[ForecastDemand],DATEADD(Calendar[Date],-7,Day))
but as you see on Nov 15 it breaks.

Thanks for trying to help me. I appreciate your effort.
Pablo
 
Hi Pablo ,

Firstly , I do not have any knowledge of Powerpivot.

Secondly , I use Excel 2007 , and have never used Powerpivot.

What I have seen Googling the problem is that there are ways in which to work around this issue ; see if these links help :

1. http://www.sqlbi.com/articles/understanding-circular-dependencies

2. http://brightgate.org/index.php?opt...erpivot-for-excel-2013&catid=35:last-24h-news

3. http://www.powerpivotpro.com/2013/01/modeling-viral-growth-and-marketing-in-powerpivot/

4. http://office.microsoft.com/en-in/excel-help/calculated-columns-in-power-pivot-HA102836183.aspx

The first one looks most promising though it does not speak of Powerpivot explicitly ; the second one discusses a work around using intermediate tables , while the third discusses an example which may be helpful.

The last link is just for information , especially since it comes from Microsoft !

Narayan
 
Thanks Narayan for your help, it's much appreciated. I did come across the 1st and 3rd links that you suggest and it's hard to apply those concepts to my model, but I will try again. I will try the other 2 as well. Thank you.
Pablo
 
You must log in or register to reply here.
Back
Top