Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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.

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.