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

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)).

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
Advocate I
MVP

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.

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

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.

MVP

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

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors