Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want a running total by date for the Amount Column which should be different for each Security ID.
Solved! Go to Solution.
Hi @NI
First add an Index Column from Query Editor or Power Query
Now you can get the expected Cumulative Values using this Calculated Column
Cumulative = CALCULATE ( SUM ( TableName[Amount] ), FILTER ( ALLEXCEPT ( TableName, TableName[Security ID] ), TableName[Index] <= EARLIER ( TableName[Index] ) ) )
Hi @NI
First add an Index Column from Query Editor or Power Query
Now you can get the expected Cumulative Values using this Calculated Column
Cumulative = CALCULATE ( SUM ( TableName[Amount] ), FILTER ( ALLEXCEPT ( TableName, TableName[Security ID] ), TableName[Index] <= EARLIER ( TableName[Index] ) ) )
I have a follow up qustion about your amazing explanation,
let's assume I have another "Country" column at the table, which I want to take as a paramater while calculating the running total, so that the cumulative total column will show not only cumulative total over time by securtiy Id but also by country, like in the example below- how should I revise the calculation in order to do so?
Thanks !
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |