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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors