cancel
Showing results 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

Helper I

## Accumulating % totals

Hi all,

I have a question regarding the calculation of accumulating % totals.

I have a table that has a list of quotes, quoted quantities, and quoted dates. I have another table with a list of orders, order quantities, and order dates. The link between the two tables are the ID number of the quote. The tables can look something like this:

 Quote ID Product Qty Quoted Date 2055F A 40 Jan 2, 2024 2055F B 15 Jan 2, 2024 2055F C 25 Jan 2, 2024 2045A A 10 Jan 15, 2024 2045A D 5 Jan 15, 2024 2000C A 8 Jan 8, 2024 2000C B 7 Jan 8, 2024 2000C C 20 Jan 8, 2024 2000C D 50 Jan 8, 2024

 Quote ID Product Qty Ordered Order Date 2055F A 20 Jan 5, 2024 2055F B 5 Feb 3, 2024 2055F C 15 Jan 18, 2024 2055F B 10 Feb 8, 2024 2045A A 8 Jan 30, 2024 2045A D 3 Feb 2, 2024 2000C A 8 Jan 20, 2024 2000C B n/a n/a 2000C C 15 Jan 10, 2024 2000C D 35 Jan 30, 2024

What I’d like to understand is how many days it takes to get <25%, <>50%, <>75%, and <100% of the order fulfilled based on the quoted volume. For example, Quote ID 2055F has a total quantity quoted of 80 units but these products were ordered at different times.

Product A was ordered first and of the 80 total units orders for that order ID, 20 units were ordered on Jan 5th, that is 25% of the quoted order was fulfilled. The number of days between order date and quoted date is 3 days.

Product C of that quote ID was ordered next with 15 units on Jan 18th. Now, the total units ordered is 35 or 44% of the quote. The number of days between order date and quoted date is 16 days.

On Feb 3rd, Product B was ordered with 5 units and the running total of the quote is now 50%. The number of days is 32 days.

Lastly, on Feb 8th, the remaining quoted units for Product B was ordered and now the running total of 50 (out of 80) or 62% of the total quoted volume. As

The result that I’d like to see is the following:

 % of units ordered Quote ID Days between Quote & Order <=25% 2055F 3 days 26%- 50% 2055F 16 days 51% - 75% 2055F 32 days (not Feb 8 b/c it this order didn’t surpass 75%) <=100% 2055F n/a

My problem is how to start to accumulate the % of units fulfilled based on the order date. Has this question been raised before? If so does someone know where I can find there answer? If not, does someone know how to perform this in Power BI/Dax?

Thank you!

Super User

How are you planning to forecast, based on which method? Working days only? Holidays? For linear regression you can use LINESTX.

Have you considered using a graphical solution?

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.

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

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

Top Solution Authors
Top Kudoed Authors