cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Circular dependency issue

 

Hi,

 

I'm trying to build a table in Power BI which includes a rolling sum columns,

but i have an issue of circular dependency.

I can build it in excel woth no problem.

 

circular.png

 

 

 

The User2 formula is = IF(User = "User2" or User = "Both", CashFlow * 2230.05/Price * IF(User = "Both", User2Pct, 1), 0)

ex: User2 (row 6) = -650000*2230.05/2235.79*35.08% = -227409

But the User2Pct formula dependes on User2 column = Sum(User1) / Sum(User2) //(start till current row - 1)

ex: User2Pct (row 6) = Sum of User 1 rows 1-5 / Sum of User 2 rows 1-5 

 

I have a measures that gives the user1 and user2, also a measure to User2Pct, but it's formula sum up the Raw columns before the multification with User2Pct - if we take the previous example it will sum the value of (-650000*2230.05/2235.79) / the same as for User1.

 

I can't figure out how to build it in power bi DAX.

Any help will be appreciated.

 

Regards,

Eran

 

Re

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks,

I implemeted it with Power Query iventually, using function to get the running total and then use it in User1Pct and User2Pct.

 

 

Regards,

Eran

View solution in original post

8 REPLIES 8
Microsoft
Microsoft

Hi @eranmn,

In the table, User2 use the User2Pct, and User2Pct use User2, its' weird? At first, I was considering there are no User2 and the User2Pct columns in your resource table? While you must create one column based on another one? So there must be one in the begginning, but both of them depands on each other, the logic is uncorrect. Because the calculation is based on columns rather than rows. Could you please share your resouce table for further analysis?

Best Regards,
Angelia

Hi Angelia,

 

thanks for your answer.

 

the logic is, if [User] column shows "User1" then the Discounted Cashflow belongs to User1 - Formula: [Cashflow] * [2230.05] / [Price]. the same logic when "User2"appears in [User] column. however when [User] column has "Both", the discounted cashflow of -650,000 which is -648,331 should be shared between them ProRata to the ratio prior to this transaction, thus if prior to a "Both" row the sum of all rows of User1 is 800 and the sum of all rows of user2 is 200, the User1Pct should be 80% and user2Pct 20%, and the "Both" row cashflow equals to 500, then the User1 should have 80% of 500, and User2 should have 20% of 500, (User1 400, User2 100), in our case the ratio for user1, 64.92% out of the -648,331 (-420,922) and 35.08% out of the  -648,331 (-227,409)
i have attached a link to an excel sheet with this logic, the excel does not have a circular dependecy as the calculation is based on a cell rather than a column. 
could you suggest how to implement this in current way or a better way to implement this logic

 

https://www.dropbox.com/s/ihkoptlrt376wmr/CircularDependencyEx.xlsx?dl=0


kind regards,
Eran

Any Ideas?

Hi @eranmn,

I download your sample table and get the User column based on the PassDate, Crashflow and Prirce. After get User, we need to calculate User1 column, but User1 depands on User1Pct, but User1Pct has no value at first, how do you get value? For example: 739869=IF(OR(E4="Both",E4="User1"),C4/D4*$D$1*IF(E4="Both",H4,1),0), if there is H4 value, how do you calculate, it will return error?

 

Please follow the steps to get user column.

1. Create a column using the formula:

Column = VAR D=Table1[Row]
RETURN CALCULATE(SUM(Table1[PassDate]),FILTER(ALL(Table1),Table1[Row]<=D))

2. Then create another calculated column using the formula.

 

User = IF(Table1[Column]=1,IF(Table1[Cashflow]<0,"Both","User2"),"User1")

1.PNG

Best Regards,
Angelia

Hi Angelia,

 

I fixed the User1Pct and User2Pct formulas:

User1Pct(first row)  = IFERROR(SUM($H10:H$10)/SUM($H10:I$10), 1)

User2Pct(first row)  =IFERROR(SUM($I10:I$10)/SUM($H10:I$10),0)

 

https://www.dropbox.com/s/ihkoptlrt376wmr/CircularDependencyEx.xlsx?dl=0

 

Regards,

Eran

Hi @eranmn,

Hi @eranmn,

I got it. The first row of User1Pct is 1, User2Pct is 0. you calculated other rows based on the first row. The basic unit is the cell. But in Power BI we calculate based on column instead of cell or row.

At first, I thought I can set User1Pct=1, User2Pct=0, then create calculated columns.

User1 = IF(OR(Table1[User]="Both",Table1[User]="User1"),Table1[Cashflow]/Table1[Price]*2230.05*IF(Table1[User]="Both",Table1[User1Pct],1),0)

 

User2 = IF(OR(Table1[User]="Both",Table1[User]="User2"),Table1[Cashflow]/Table1[Price]*2230.05*IF(Table1[User]="Both",Table1[User2Pct],1),0)


1.PNG2.PNG

Then I try to fix User1Pct and User2Pct, we can't get expected result, which caused by Circular dependency issue. The basic unit in Power BI is column. So you'd better get expected result in excel, then can upload the result to Power BI desktop.

3.png

Best Regards,
Angelia

Best Regards,
Angelia

 

Thanks,

I implemeted it with Power Query iventually, using function to get the running total and then use it in User1Pct and User2Pct.

 

 

Regards,

Eran

View solution in original post

Hi @eranmn,

Congratulations, you have resolved your issue by yourself. Please mark the right reply as answer, or welcome to share your detailed workaround, so that more people will benefit from here.

Best Regards,
Angelia

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors