topic Re: Trend line by calculating custom summation in DAX Commands and Tips

Trend line by calculating custom summation

pbinewbie47 — Fri, 10 May 2024 01:23:48 GMT

Hello,

I have table that looks like :

Name	Order_No	count
A	100	45
B	104	55
C	110	25
C	115	20
A	116	40
B	125	30
C	135	10
B	140	15

I want to plot a trend of count vs Order no. as the final goal. So for each order no., it looks at the entry in the table for each unit before that order no. (smaller than current order no.). Adds the value of count for each unit, and that becomes the count to plot the trend. My final table to plot the data should look something like this :

Final count	Order no
45 + 0 + 0	100
(45 + 55 + 0)	104
(45 + 55 + 25)	110
(45 + 55 + 20)	115
(40 + 55 + 20)	116
(40 + 30 + 20)	125
(40 + 30 + 10)	135
(40 + 15 + 10)	140

How can I write DAX measure for the final count? TIA.

Re: Trend line by calculating custom summation

johnbasha33 — Fri, 10 May 2024 09:49:15 GMT

Hi pbinewbie47 try below measure and let us know what happened.
Final Count =
VAR CurrentOrderNo = 'YourTable'[Order_No]
RETURN
SUMX(
FILTER(
'YourTable',
'YourTable'[Order_No] <= CurrentOrderNo
),
'YourTable'[count]
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Trend line by calculating custom summation

hackcrr — Fri, 10 May 2024 12:57:22 GMT

Hi, pbinewbie47

Here is the example data I used:

You can try the following DAX expressions:

MEASURE = VAR _table = SUMMARIZE ( 'Table', 'Table'[Order_No], "final count", CALCULATE ( SUM ( 'Table'[count] ), FILTER ( ALL ( 'Table' ), 'Table'[Order_No] <= EARLIER ( 'Table'[Order_No] ) ) ) ) VAR _current_No = SELECTEDVALUE ( 'Table'[Order_No] ) RETURN SUMX ( FILTER ( _table, 'Table'[Order_No] = _current_No ), [final count] )

The results are as follows:

In addition, you can use the calculation table shown below:

Best Regards,

hackcrr

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

Re: Trend line by calculating custom summation

pbinewbie47 — Fri, 10 May 2024 17:44:58 GMT

Thank you for your response. Something is off in the calculations.

Posting the image of the real dataset with what is expected and what is happening in your solution.
The type column is just for example. There are multiple entries for the same unit and order_no with different types in the table. I want to sum all of them, no filtering on types. So just for information.

Re: Trend line by calculating custom summation

pbinewbie47 — Fri, 10 May 2024 17:48:15 GMT

Thank you for your solution. I tried the solution and something is way off in the calculation.
The numbers are inflated and not matching the magnitude.

The type column is just for additonal info, nothing changes in my calculation

Re: Trend line by calculating custom summation

Anonymous — Tue, 14 May 2024 09:05:42 GMT

Hi pbinewbie47 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate the sales.

Final count = var _name=SELECTEDVALUE('Table'[Name]) var _order=SELECTEDVALUE('Table'[Order no]) var _preordera=CALCULATE(MAX('Table'[Order no]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "A"&&'Table'[Order no] <= _order)) var _preorderb=CALCULATE(MAX('Table'[Order no]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "B"&&'Table'[Order no] <= _order)) var _preorderc=CALCULATE(MAX('Table'[Order no]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "C"&&'Table'[Order no] <= _order)) var _precounta=CALCULATE(MAX('Table'[Count]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "A"&&'Table'[Order no] = _preordera)) var _precountb=CALCULATE(MAX('Table'[Count]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "B"&&'Table'[Order no] = _preorderb)) var _precountc=CALCULATE(MAX('Table'[Count]),FILTER(ALLSELECTED('Table'),'Table'[Name] = "C"&&'Table'[Order no] = _preorderc)) return _precounta + _precountb + _precountc //_precounta&"+"&_precountb&"+"&_precountc

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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