Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heidibb
Helper IV
Helper IV

Running Total, Not Date Based

Hello,

I have a table that I'm trying to get a running total on each row for the previous 6 rows.

For example, for the TermAxis 0 row (21EW6), I want to sum the In-term leads for the rows with termaxis = 0, -1, -2, -3, -4 and -5

I have a field looking for the min term axis and I'm trying to sum where term axis >= MinTermAxis, but that doesn't seem to work. for me.

Any ideas on how I might go about this?

.Capture.PNG

9 REPLIES 9
aj1973
Community Champion
Community Champion

Hi @heidibb 

Is this what you want?

aj1973_0-1629920358790.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

HI AJ,

 

Not exactly -- one thing I didn't mention this is also at the academic level as the term axis restarts based on Academic Level. I'm sorry I didn't mention that. 

 

So, based on that, just looking at UG rows only and sorting on term axis -- my six term totals would be as follows (last column)

 

heidibb_0-1629921440865.png

 

Hi,

Share the download link of your MS Excel workbook and show your expected result there via a formula very clearly.  Also, do you want a measure solution or a calculated column solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hopefully you can see the file here:

https://docs.google.com/spreadsheets/d/1nfKyzRQcJmi_o4vRwRaNgPY0TZJtjhPE/edit?usp=sharing&ouid=10276...

 

Formula is in the last column. It's a simple sum of six rows. File is sorted by academic level and then by term axis so the correct six rows are being summed.

 

It would be a measure solution that I'm looking for.

 

Thank you for taking a look!

Hi,

I get an Access Denied message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
heidibb
Helper IV
Helper IV

Here is some sample data in a copy/paste format if needed!

 

AcademicLevelCodeTermTermAxisMinTermAxisSum In-term Leads
GR17TW4-20-2423067
GR17TW5-19-2322339
GR17TW1-18-2223323
GR17TW2-17-2120277
GR18TW3-16-2023781
GR18TW4-15-1921107
GR18TW5-14-1826371
GR18TW1-13-1723420
GR18TW2-12-1620384
GR19TW3-11-1525985
GR19TW4-10-1424766
GR19TW5-9-1323288
GR19TW1-8-1222067
GR20TW3-7-1123991
GR20TW4-6-1023145
GR20TW5-5-932189
GR20TW1-4-835398
GR20TW2-3-724983
GR21TW3-2-634249
GR21TW4-1-526249
GR21TW50-428968
UG17EW6-24-2944849
UG17EW1-23-2854718
UG17EW2-22-2742965
UG18EW3-21-2655702
UG18EW4-20-2561369
UG18EW5-19-2457039
UG18EW6-18-2362101
UG18EW1-17-2280622
UG18EW2-16-2157543
UG19EW3-15-2070901
UG19EW4-14-1964923
UG19EW5-13-1867889
UG19EW6-12-1769548
UG19EW1-11-1681491
UG19EW2-10-1566914
UG20EW3-9-1475910
UG20EW4-8-1377401
UG20EW5-7-1278476
UG20EW6-6-1184550
UG20EW1-5-10105522
UG20EW2-4-976644
UG21EW3-3-891358
UG21EW4-2-798226
UG21EW5-1-687241
UG21EW60-588605
aj1973
Community Champion
Community Champion

Columns TermAxis and MinTermAxis! what do they represent? where are they coming from?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

TermAxis is a database field to sort our terms chronologically. It continually updates so the ongoing term is always 0. The most recently completed term is -1 and so on.

 

MinTermAxis was my attempt to create a field I could use in a calculation to try and sum InTermLeads where the TermAxis was between MinTermAxis and TermAxis.

 

So, for example, for 21EW6 (UG TermAxis 0), I want to sum the InTermLeads for all rows that have a UG term axis of 0, -1, -2, -3, -4, and -5.

 

Does that help? I know it can be confusing!

aj1973
Community Champion
Community Champion

Hi @heidibb 

Is this starting looking good!

aj1973_1-1630100323705.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.