cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bidgeir
MVP

Calculating plan accuracy, circular reference

Hi all

 

I´m trying to calculate plan accuracy where I can "use" orders from +-1 week to fulfill the plan. As you can see in the image below I have 13 weeks. In those 13 weeks I have plan in column C and orders in column B. Columns A, B and C are my data, the rest is what I want to calculate. In column E I have calculated the Orders +-1 week but taking into account if there are any leftover from previous period. The problem is that the leftover are reliant on Orders +-1 week. This is possible in Excel because in the Orders +-1 week (column E) I reference leftover (column F) from the row above. So the leftover formula for line 3 is E3-C3 and the Orders +-1 week for line 3 is B4+IF(F2>SUM(B2:B3),SUM(B2:B3),IF(F2>0,F2,0)). 

 

bidgeir_0-1615203973704.png

 

In Power BI it´s not possible to reference a column/measure that relies on the source column/measure. 

 

The goal are column G, H and I using only columns A, B and C. So my main goal is column H line 15 (the total accuracy) but I would like to calculate accuracy pr. line and covered batches as well. 

 

The datasource is not Excel and not in this exact format but it´s reprecentative of the data source.

 

If anyone has an idea about how to go on about solving this in Power BI I would be very greatful. I don´t care if the solution is in Power Query or DAX. You can download the Excel file with this example and another one here: https://tinyurl.com/4b55kaxh  

 

I have just fiddled a little with it in Power BI so I don´t have any PBIX to share. 

 

/Ásgeir

5 REPLIES 5
bertalan_ronai
Advocate I
Advocate I

Good try but unfortunately not what I need. I have come to the conclusion that it´s not possible (at least with my knowledge) so I´m not going to calculate it in this way. I going for a less accurate but good enough method of looking at the total period instead of focusing on each week.

bidgeir
MVP

I have created a PBIX with the Excel imported if anyone prefers not to import then Excel data. You can find the PBIX here: https://tinyurl.com/yutx7st9 

bertalan_ronai
Advocate I
Advocate I

Hi!

If you don't reference a measure but use the same DAX in the other measure as a variable then it might solve the referencing problem. I do admit it is just a quick guess on my part.

I simple cannot see how that is possible. The calculation for one use the other one and vice versa. But maybe I´m just blocked in my mind after looking at it from a certain point of view for too long

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors