Forum Discussion

locle90's avatar
locle90
Icon for Helper I rankHelper I
4 years ago
Solved

Total Accumulated without data

Dear All, I have fomule to calculate the count of item with leadtime days 0-14.But some days we don't have the order then  i need day 7,8,9 should show with data 79. Could you help to advice how i ...
  • PurpleGate's avatar
    PurpleGate
    4 years ago

    As Lead time is 0-14 days,  I made a raw table called OrderLeadTime just 15 rows with the numbers 0-14

    Then I linked as a 1-* to your calculated column "Order Lead Time" in table LeadTime

     

    xx cumul = 
    var c = max(OrderLeadTime[OLT_Raw])
    var t = CALCULATE(DISTINCTCOUNT(Leadtime[Item]),REMOVEFILTERS(OrderLeadTime[OLT_Raw]))
    var td = CALCULATE(DISTINCTCOUNT(Leadtime[Item]),OrderLeadTime[OLT_Raw]<=c)
    return divide(td,t,1) 

       

PurpleGate's avatar
PurpleGate
Icon for Resolver III rankResolver III
4 years ago

 

 
 
 

How is your data set up?

My "count" column is put in as a sum

 

I think you might need to create a measure for your "Count of item" column and then refer to that.

Try:

Count of item = CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[OLT]))

and then do

VN OLT 2 = CALCULATE([Count of item],FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

 

 

In my column called "Measure", the formula is saying

sum up the [count] column in order of the [OLT] column

Measure = CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))

 

 

In my column called [vn olt], the formula is saying

if there is data in the [count] column, sum up the [olt] column, in the order of [olt] column

VN OLT = CALCULATE(DISTINCTCOUNT('Table'[Count]),FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))
  • locle90's avatar
    locle90
    Icon for Helper I rankHelper I
    4 years ago

    Dear PurpleGate,

    I found a different that my count of item :

    Count of Item = DISTINCTCOUNT(Leadtime[Item])
    I need to count the order and show that on the chart not SUM.
    So i still don't know how to fix it 
    PREVIEW
     
     
     
    • PurpleGate's avatar
      PurpleGate
      Icon for Resolver III rankResolver III
      4 years ago

      From what I understand, you want to see a rolling total?

      If days 7, 8 and 9 should all show the value 79?

      In that case, SUM would be best as it is adding up all the numbers prior to the current row.

       

       

      If that isnt correct, perhaps you could write in the numbers you expect to see and then we can figure out a solution to get them?