This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
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
Hi 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
Tom, 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).
Have you considered Power Query? I have seen some clever things done with running totals and offsets.
The forum ‘Power Pivot’ is closed to new topics and replies.