Forum Discussion
Helper ITotal 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 can change my DAX ?
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)
12 Replies
PurpleGateResolver III
Hi,
Try this measure
Measure = CALCULATE(SUM('Table'[Count]),FILTER(ALL('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))
locle90Dear PurpleGate,
My DAX count mean : " count of item on data list". So i can use the Sum fomula like you.
Could you refe DAX bellow and give your adivice.
VN OLT = CALCULATE (DISTINCTCOUNT(Leadtime[Item]),FILTER (ALLSELECTED(Leadtime),Leadtime[OLT] <= MAX( Leadtime[OLT])))- PurpleGate
Hi Locle90,
When I use that formula, it works to count the rows with data. I have 11 rows in total and 8 of them have data in them in the "count" column.
I put below the result. It works well, if this is what you are after!
VN OLT = CALCULATE(DISTINCTCOUNT('Table'[Count]),FILTER(ALLSELECTED('Table'[OLT]),'Table'[OLT]<=MAX('Table'[OLT])))
- PurpleGate
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])))
- PurpleGate
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?
- locle90
Dear,
This is my DAX and my wish result.
- PurpleGate
I see. If your column is a measure, and you cannot use SUM then how about this:
Wish Result = CALCULATE([insert your count measure here],FILTER(ALL('Leadtime'[OLT]),'Leadtime'[OLT] <= MAX ('Leadtime'[OLT])))- locle90
Dear PurpleGate,
Thanks for your strongly support but my result still same with my expactation .
- PurpleGate
try removing "distinct count" from your measure.
If this is referring to a column, then you can use sum, and if it is referring to a measure, you can just write the measure name.
- PurpleGate
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)