topic Re: Total Accumulated without data in DAX Commands and Tips

Total Accumulated without data

locle90 — Thu, 11 Aug 2022 07:46:08 GMT

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 ?

Re: Total Accumulated without data

PurpleGate — Thu, 11 Aug 2022 13:22:50 GMT

Hi,

Try this measure

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

Re: Total Accumulated without data

locle90 — Mon, 15 Aug 2022 04:18:45 GMT

Dear 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])

))

Re: Total Accumulated without data

PurpleGate — Mon, 15 Aug 2022 07:03:13 GMT

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])))

Re: Total Accumulated without data

locle90 — Mon, 15 Aug 2022 07:21:19 GMT

Dear PurpleGate,

I tried with your DAX but the result still same.Could you help to share the fomula of measure ?

Re: Total Accumulated without data

PurpleGate — Mon, 15 Aug 2022 07:56:46 GMT

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])))

Re: Total Accumulated without data

locle90 — Mon, 15 Aug 2022 08:27:54 GMT

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

Re: Total Accumulated without data

PurpleGate — Mon, 15 Aug 2022 08:45:59 GMT

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?

Re: Total Accumulated without data

locle90 — Mon, 15 Aug 2022 09:01:02 GMT

Dear,

This is my DAX and my wish result.

Re: Total Accumulated without data

PurpleGate — Mon, 15 Aug 2022 09:35:48 GMT

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])))

Re: Total Accumulated without data

locle90 — Mon, 15 Aug 2022 09:43:20 GMT

Dear PurpleGate,

Thanks for your strongly support but my result still same with my expactation .

Re: Total Accumulated without data

PurpleGate — Mon, 15 Aug 2022 10:12:20 GMT

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.

Re: Total Accumulated without data

PurpleGate — Thu, 18 Aug 2022 10:21:26 GMT

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)