Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi,
How can I write a measure to get the running total for this below case? I know how to write for a calculated column, just wondering how it will be with a measure.
Thanks
Column1 | Column3 | ExpectedResult |
A | 2 | 2 |
A | 3 | 5 |
A | 1 | 6 |
B | 3 | 3 |
B | 4 | 7 |
B | 5 | 12 |
B | 6 | 18 |
Solved! Go to Solution.
Hi @Iamnvt
Here you go.
RunningTotal Measure = CALCULATE(
SUM( MovingSum[Column2]),
FILTER( ALL(MovingSum) ,
SUMX( FILTER( MovingSum, EARLIER( MovingSum[Column1] ) = MovingSum[Column1] && EARLIER(MovingSum[Index]) <= MovingSum[Index] ), MovingSum[Column2] )
)
)
This assumes you have created an index column using query editor.
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi @Iamnvt,
The quickmeasure as suggested @Melvlee will not work because it will reset at every row of Column1.
The method suggested by @Ashish_Mathur will compute but will fail in display. The rows will not be displayed in the same order as original data table.
If you want the values to be displayed in the same order as your input data you will have to bring in the index column defined in the excel work sheet by @Ashish_Mathur and sort in the table display by index column.
If this works for you please accept @Ashish_Mathur suggestion as a solution and do give KUDOS to me as well.
Cheers
CheenuSing
hey, I want to write a MEASURE (Calculated field), not a Calculated column.
is there any solution for that?
Hi @Iamnvt
Here you go.
RunningTotal Measure = CALCULATE(
SUM( MovingSum[Column2]),
FILTER( ALL(MovingSum) ,
SUMX( FILTER( MovingSum, EARLIER( MovingSum[Column1] ) = MovingSum[Column1] && EARLIER(MovingSum[Index]) <= MovingSum[Index] ), MovingSum[Column2] )
)
)
This assumes you have created an index column using query editor.
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi,
I am assuming that you want to ccreate a calculated column formula. If that be the case, you may refer to my solution here. Go the Data Model to see the result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |