Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I need some help with a circular reference problem that I have been working on for a few weeks. I believe that this can be done, it is just that I am still in the process of learning power bi and I am really stuck. I have attached an example of my problem.
At the start of the month, values can be added or subtracted. There is a beginning month fund value which is equal to the previous month end value plus any additions and subtractions.
The end of the month fund value is an external input. However the customer's month end value is calculated using the % ownership of that customer from the previous month end.
The only values which are inputs are the following columns: Adds, Subs, and Month End Fund Value. Everything else is calculated which is how it becomes a circular reference problem.
Any suggestions or ideas would be really appreciated. Thank you so much.
NOTES | ADDS and SUBS will be uploaded inputs | Ownership % on ME does not change from BM | From previous month end + adds + subs | For the purpose of this calculation, this is hard coded | ||||||||||
Customer | Date | Adds | Subs | BM Cust Value | ME Cust Value | % Ownership | Beginning Month Fund Value | Month End Fund Value | CHECK | |||||
C1 | 1/1/2017 | $ 50.00 | $ 50.00 | 47.6% | $ 105.00 | 100.0% | ||||||||
C2 | 1/1/2017 | $ 25.00 | $ 25.00 | 23.8% | $ 105.00 | |||||||||
C3 | 1/1/2017 | $ 30.00 | $ 30.00 | 28.6% | $ 105.00 | |||||||||
C1 | 1/31/2017 | $ 50.48 | $ 106.00 | |||||||||||
C2 | 1/31/2017 | $ 25.24 | $ 106.00 | |||||||||||
C3 | 1/31/2017 | $ 30.29 | $ 106.00 | |||||||||||
C1 | 2/1/2017 | $ 5.00 | $ 55.48 | 45.8% | $ 121.00 | 100.0% | ||||||||
C2 | 2/1/2017 | $ 5.00 | $ 30.24 | 25.0% | $ 121.00 | |||||||||
C3 | 2/1/2017 | $ 5.00 | $ 35.29 | 29.2% | $ 121.00 | |||||||||
C1 | 2/28/2017 | $ 58.25 | $ 127.05 | |||||||||||
C2 | 2/28/2017 | $ 31.75 | $ 127.05 | |||||||||||
C3 | 2/28/2017 | $ 37.05 | $ 127.05 | |||||||||||
C1 | 3/1/2017 | $ (7.00) | $ 51.25 | 45.7% | $ 112.05 | 100.0% | ||||||||
C2 | 3/1/2017 | $ (5.00) | $ 26.75 | 23.9% | $ 112.05 | |||||||||
C3 | 3/1/2017 | $ (3.00) | $ 34.05 | 30.4% | $ 112.05 | |||||||||
C1 | 3/31/2017 | $ 59.64 | $ 130.40 | |||||||||||
C2 | 3/31/2017 | $ 31.13 | $ 130.40 | |||||||||||
C3 | 3/31/2017 | $ 39.63 | $ 130.40 | |||||||||||
C1 | 4/1/2017 | $ 59.64 | 45.7% | $ 130.40 | 100.0% | |||||||||
C2 | 4/1/2017 | $ 31.13 | 23.9% | $ 130.40 | ||||||||||
C3 | 4/1/2017 | $ 39.63 | 30.4% | $ 130.40 | ||||||||||
C1 | 4/30/2017 | $ 64.07 | $ 140.07 | |||||||||||
C2 | 4/30/2017 | $ 33.44 | $ 140.07 | |||||||||||
C3 | 4/30/2017 | $ 42.57 | $ 140.07 | |||||||||||
C1 | 5/1/2017 | $ 5.00 | $ 69.07 | 44.5% | $ 155.07 | 100.0% | ||||||||
C2 | 5/1/2017 | $ 5.00 | $ 38.44 | 24.8% | $ 155.07 | |||||||||
C3 | 5/1/2017 | $ 5.00 | $ 47.57 | 30.7% | $ 155.07 | |||||||||
C1 | 5/31/2017 | $ 65.51 | $ 147.08 | |||||||||||
C2 | 5/31/2017 | $ 36.46 | $ 147.08 | |||||||||||
C3 | 5/31/2017 | $ 45.11 | $ 147.08 | |||||||||||
C1 | 6/1/2017 | $ 65.51 | 44.5% | $ 147.08 | 100.0% | |||||||||
C2 | 6/1/2017 | $ 36.46 | 24.8% | $ 147.08 | ||||||||||
C3 | 6/1/2017 | $ 45.11 | 30.7% | $ 147.08 | ||||||||||
C1 | 6/30/2017 | $ 68.78 | $ 154.43 | |||||||||||
C2 | 6/30/2017 | $ 38.28 | $ 154.43 | |||||||||||
C3 | 6/30/2017 | $ 47.37 | $ 154.43 |
@ARob198 , refer if this can help
https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
I think the DAX formulas will have to be provided and relationships ( if there are more than one table) so we can see what's going on.
Also, have you tried calculating the columns in Power Query?
There is not another table, nor are there any DAX formulas. I am happy to share this excel file if you tell me how to do that on the forum. I am trying to recreate this table and calculations in DAX. What is the difference between doing the calulcations as measures in the desktop and doing them in Power Query Editor? I was under the impression that calculations should not be done in Power Query Editor if at all possible.
Thank you for your help HotChilli
I'm slightly confused. Maybe there is a misunderstanding about terminology.
There's a circular reference error (that's the title of the post). There are no other tables. The circular reference must be coming from the DAX formula for a calculated column (probably based on another calculated column). So i'd like to see the DAX formula for (any) calculated columns. Let me know if I've got that wrong.
Power Query is an excellent tool and works beautifully for this sort of thing.
The difference between calculated columns and measures->
https://radacad.com/measure-vs-calculated-column-the-mysterious-question-not
Sure- how do I share a file with you?
Thank you
You can upload to a 3rd party sharing site and post the link (or send it the link via messages - on this site- to me if you don't want it public)
Here is my link to the file with the circular reference.
The beginning of the month values are based on the values from the previous month end. So values on 2/1/2017 will be pulled from the values on 1/31/2017 then any new additions or subtractions are added and the % ownership is calculated. The month end values are calculated using the % ownership from the start of the month multiplied by the month end value for the fund. Please let me know if you have any other questions.
Thank you for your help.
@ v-lionel-msft
I have posted the PBIX file. Is it possible for you to take a look? I believe there is a way to do this, I am just unsure of how to format the examples that I have found to fit my data. Here is one example: https://www.sqlbi.com/articles/computing-the-future-value-of-an-investment-based-on-compound-growth-...
Would adding a period identifier help? For example, January could be period 1, Feb period 2, etc?
Thank you
Thank you so much!
I see these are not columns, these are measures.
The circular reference is caused by measure
depending on
which depends on
Sure- that is what I was trying to explain in the first post in this thread. I have attached the excel file behind the picture in the first post. Here is the link: link
It is already a very simplified version of the excel model that I need to use. I know that this is possible in Power BI as I have found examples of people creating sort of similar models like the link in the posting above. I am just very new at DAX/Power BI so I am not quite sure how to create them with my data. Any thoughts or suggestions that anyone has would be very helpful.
Thank you so much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |