Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |