Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pbinewbie47
Regular Visitor

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @pbinewbie47 ,

Based on my testing, please try the following methods:

1.Create the simple table.

vjiewumsft_0-1715677432449.png

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.

vjiewumsft_1-1715677449622.png

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.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @pbinewbie47 ,

Based on my testing, please try the following methods:

1.Create the simple table.

vjiewumsft_0-1715677432449.png

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.

vjiewumsft_1-1715677449622.png

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
Super User
Super User

Hi, @pbinewbie47 

Here is the example data I used:

hackcrr_0-1715345550680.png

 

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:

hackcrr_1-1715345576762.png

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

hackcrr_2-1715345808880.png

 

 

Best Regards,

hackcrr

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

 

 

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

pbinewbie47_1-1715363245108.png

 

johnbasha33
Super User
Super User

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

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.

pbinewbie47_0-1715363041588.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.