Forum Discussion

pbinewbie47's avatar
pbinewbie47
Regular Visitor
2 years ago
Solved

Trend line by calculating custom summation

Hello,

I have table that looks like :

NameOrder_Nocount
A10045
B10455
C11025
C11520
A11640
B12530
C13510
B14015


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 countOrder no
45 + 0 + 0100
(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.

Cumulative Calculation
DAX Measures
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • johnbasha33's avatar
    johnbasha33
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      pbinewbie47
      Regular Visitor
      2 years ago

      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.

       

  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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's avatar
      pbinewbie47
      Regular Visitor
      2 years ago

      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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.