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 all,
I'm trying to get a running total as in the table below, would need the formula for the column Running Total.
I need to use Direct Query, already have Unrestricted Measures activated.
- My running total needs to be filtered per "Company" (e.g. 640, 641, 642, always numerical),
- My time horizon is labelled as "Period" (so not in a date format, but a numerical 1 ,2 ,3, 4 ....)
- I can have multiple rows with values for 1 period (e.g. 3 separate rows for company 640 in period 2)
=> the "Running Total" column should report the summed running total at the end of a period (as e.g. in period 2 for 640)
Struggling to get this to work, all help is welcome!
Thanks already!
Company | Period | Investment Amount | Running Total |
640 | 1 | 100 | 100 |
640 | 2 | 50 | 250 |
640 | 2 | 50 | 250 |
640 | 2 | 50 | 250 |
640 | 3 | 150 | 400 |
641 | 1 | 1 | 1 |
641 | 2 | 5 | 6 |
641 | 3 | 10 | 16 |
642 | 1 | 3000 | 3000 |
642 | 2 | 2000 | 5000 |
642 | 3 | 5000 | 10000 |
Solved! Go to Solution.
@tomvs wrote:
Hi all,
I'm trying to get a running total as in the table below, would need the formula for the column Running Total.I need to use Direct Query, already have Unrestricted Measures activated.
- My running total needs to be filtered per "Company" (e.g. 640, 641, 642, always numerical),
- My time horizon is labelled as "Period" (so not in a date format, but a numerical 1 ,2 ,3, 4 ....)
- I can have multiple rows with values for 1 period (e.g. 3 separate rows for company 640 in period 2)
=> the "Running Total" column should report the summed running total at the end of a period (as e.g. in period 2 for 640)
Struggling to get this to work, all help is welcome!
Thanks already!
Company Period Investment Amount Running Total 640 1 100 100 640 2 50 250 640 2 50 250 640 2 50 250 640 3 150 400 641 1 1 1 641 2 5 6 641 3 10 16 642 1 3000 3000 642 2 2000 5000 642 3 5000 10000
Try a measure like
RUNNING TOTAL = CALCULATE ( SUM ( YOURTABLE[Investment Amount] ), FILTER ( ALLEXCEPT ( YOURTABLE, YOURTABLE[Company] ), YOURTABLE[period] <= MAX ( YOURTABLE[period] ) ) )
@tomvs wrote:
Hi all,
I'm trying to get a running total as in the table below, would need the formula for the column Running Total.I need to use Direct Query, already have Unrestricted Measures activated.
- My running total needs to be filtered per "Company" (e.g. 640, 641, 642, always numerical),
- My time horizon is labelled as "Period" (so not in a date format, but a numerical 1 ,2 ,3, 4 ....)
- I can have multiple rows with values for 1 period (e.g. 3 separate rows for company 640 in period 2)
=> the "Running Total" column should report the summed running total at the end of a period (as e.g. in period 2 for 640)
Struggling to get this to work, all help is welcome!
Thanks already!
Company Period Investment Amount Running Total 640 1 100 100 640 2 50 250 640 2 50 250 640 2 50 250 640 3 150 400 641 1 1 1 641 2 5 6 641 3 10 16 642 1 3000 3000 642 2 2000 5000 642 3 5000 10000
Try a measure like
RUNNING TOTAL = CALCULATE ( SUM ( YOURTABLE[Investment Amount] ), FILTER ( ALLEXCEPT ( YOURTABLE, YOURTABLE[Company] ), YOURTABLE[period] <= MAX ( YOURTABLE[period] ) ) )
@Eric_Zhang My good vibes of the day go to you my friend, works perfectly and makes perfect sense looking at it in hindsight. Can leverage this to solve some other issues as well, thanks a lot!
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |