Need Help Running Total Based on two columns

[namjan]

Hi

I have a WEBI Report which is running correctly. I need to build a PowerBI Dashboard. Mostly I am able to replicate, but I am stuck on Running Total formula. I am not able to figure out how to write this in Power BI, as all the rest of the formulas are based on this formula.

WEBI Formulas

RunTotal =   [Qty Available]  -   RunningSum(  [Qty to be Delivered] ; Row; (  [Plant] ; [Material] )  )

Fully Filled = If ( [Qty Delivered] >=[Qty Ordered] ;1 ; If (RunTotal] >0 ;1;If ([RunTotal]=0 And [Qty to be Delivered]=[Qty Ordered];1;0)))

Any Ideas. Thanks

[TheGreatYOLO]

Fully Filled might look something like this

Q Delivered = SUM(Qty_Delivered)
Q Ordered = SUM(Qty Ordered)


Fully Filled = If(([Q Delivered] >=[Q Ordered]) || ([Run Total] > 0) || (([Run Total] = 0) && ( [Q Delivered]=[Q Ordered])),"1","0")

 || is how you do an OR logic

&& is how you do an AND logic

[namjan]

Hi

Thanks for the reply. I would just like to add-- This data is only for one day and shows the material stock in a plant. Every day it will get refreshed once in the morning that willl tell the user how many orders can be filled for that material and in which plant. Also shows the status which plant has stock available for that material and If we need to transfer or deliver the material from any other plant.

I still think my RunTotal should be based on Plant and Material and not on Order No.

Please advise.

Thanks

[TheGreatYOLO]

You could add a calculated column for the Plant and Material

Plant and Mat = Plant & Material 

Measure

Run Total = CALCULATE(SUM(Qty Available) - SUM(Qty to be Delivered), FILTER(ALLEXCEPT(Order Number, Plant and Mat), Order Number <= MAX(Order Number) && Plant and Mat = MAX(Plant and Mat)))

These max forulas dont often work great when summarising data so it may work well in detial but the totals may not work. There are people much better at DAX who may have a better idea then this one

[TheGreatYOLO]

Hi Namjan,

You may need to convert your Order Number into a number and remove the P

Create a calculated column in the data table

Order Number = MID(Order No,2,9)

It would look a little like

Run Total = CALCULATE(SUM(Qty Available) - SUM(Qty to be Delivered), FILTER(ALL(Order Number), Order Number <= MAX(Order Number)))

That is what I am guessing the Run Total would be