Trend line by calculating custom summation

[pbinewbie47]

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.

[Anonymous · Accepted solution]

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.

[hackcrr]

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.

[pbinewbie47]

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

[johnbasha33]

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 !!

[pbinewbie47]

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.