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
Cali_2020
Helper I
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!

1 REPLY 1
lbendlin
Super User
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?

lbendlin_0-1713640081985.png

 

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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