Forum Discussion

sabd80's avatar
sabd80
Helper IV
3 years ago

Running Total until hits target

Hi,

 

I have below sample data.

I want to calculate the percentage of quntity dilevered until the cummulative quantity hits the target.

so, line 5 Quantity Delivered running total is <= Target, so this is included

line 6 Quantity Delivered running total is <= Target, so this is included

line 7 Quantity Delivered running total is on the borderline of the target it needs to be included

line 8 and 9 Quantity Delivered running total is > Target, so this is excluded

I need to calculate Met Target, in the attached excel the calculation is SUM(C5:C7)/SUM((B5:B7))

 

How can I do this in DAX?

Need Help

10 Replies

  • sabd80's avatar
    sabd80
    Helper IV
    3 years ago

    Just to add another point, each week has a target value, so when we compare the target with Quantity Delivered running total , both have to be in the same period.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    if you want to do this week wise then there should be a Date column in your Target and Actual table.  Share the download link of yor PBI file with both these tables having a proper Date column.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Upload the file on Google Drive/One Drive and share the download link.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  sabd80 ,

     

    Here are the steps you can follow：

    1. Create measure.

    QuantityDelivered running total =
var _all=
SUMX(FILTER(ALL('Table'),'Table'[Order Number] <=MAX('Table'[Order Number])),[Quantity Delivered])
var _table=
SUMMARIZE('Table','Table'[Order Number],'Table'[Qaunty Ordered],'Table'[Quantity Delivered],"QuantityDelivered running total",
SUMX(FILTER(ALL('Table'),'Table'[Order Number]<=EARLIER('Table'[Order Number])),[Quantity Delivered]))
var _table1=
ADDCOLUMNS(_table,"Min",MINX(FILTER(_table,[QuantityDelivered running total]> 150),[Order Number])
)
var _sum1=
SUMX(FILTER(ALL('Table'),'Table'[Order Number]<=MINX(_table1,[Min])),[Quantity Delivered])
var _sum2=
SUMX(FILTER(ALL('Table'),'Table'[Order Number]<=MINX(_table1,[Min])),[Qaunty Ordered])
return
IF(
    HASONEVALUE('Table'[Order Number]),_all,
DIVIDE(_sum1,_sum2))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • sabd80's avatar
      sabd80
      Helper IV
      3 years ago

      thanks Anonymous , I have tried your measure, it is very slow because my data is very large.

       

      thanks for your effort.