Home › Forums › Power Pivot › Circular Reference Calculated Column – Help needed
This topic contains 3 replies, has 2 voices, and was last updated by tomallan 6 years, 7 months ago.
-
AuthorPosts
-
July 26, 2017 at 9:14 pm #8188
I have a table with customer status transitions… a row represents a transition in a customer’s status. The attached sample file has data for one customer, who has 117 rows (so a 117 status transitions — each time his status changed (1x per day only) a record was written.
Each status transition results in either a Positive and Negative impact in the overall count of customers.
Problem is the net of the “raw” impact in the data can often result in a value other than 1 or zero. And a customer can only be active or not, not extra active since the net of the transitions result in a higher value.
what I’m trying to do is add a calculated column for the Adjusted Impact that looks at the running sum of the adjusted impact for the rows prior to the current,
IF Sum = 1, and the Impact = 1 force the Impact = 0
ELSE IF Sum = 0, and the Impact = -1, force the impact = 0
otherwise use the Raw Impact.I was able to do this in excel (see attached file — column F “Adjusted Impact”
However, any attempt to do this in PowerPivot as a calculated column results in a circular reference error.The running sum (column G) is added just to prove that my adjusted impact is correct, and never results in a value other than 1 or 0.
Once I have the Adjusted Impact, we have a measure that will sum the column thru a date (point in time) that will get the count of active customers at that time. (that measure works if I I have correct data in the “Impact” — thus why I need this adjusted impact).
any help is appreciated
Attachments:
You must be logged in to view attached files.July 27, 2017 at 5:25 am #8190Hi Debbie,
Thanks for reaching out with your question.
When Power Pivot writes to a column it commits to making a single pass through the column without looking backward or forward in the same column (side to side at other columns is OK, but not above or below in the same column). Your procedure requires looking back in the same column to write its output.
It is correct to do such a data preparation task in a tool like Excel and then import into Power Pivot for later analysis.
Tom
July 27, 2017 at 1:06 pm #8193Tom, thanks for your reply. The volume of data is too large to do this in Excel. and this table is part of a much larger tabular model, which is refreshed daily, so don’t see this is something that can be done in Excel (outside of the model).
July 28, 2017 at 1:22 am #8198Have you considered Power Query? I have seen some clever things done with running totals and offsets.
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.