Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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.
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.
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
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |