Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
.
Hi @heidibb
Is this what you want?
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)
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?
Hopefully you can see the file here:
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.
Here is some sample data in a copy/paste format if needed!
AcademicLevelCode | Term | TermAxis | MinTermAxis | Sum In-term Leads |
GR | 17TW4 | -20 | -24 | 23067 |
GR | 17TW5 | -19 | -23 | 22339 |
GR | 17TW1 | -18 | -22 | 23323 |
GR | 17TW2 | -17 | -21 | 20277 |
GR | 18TW3 | -16 | -20 | 23781 |
GR | 18TW4 | -15 | -19 | 21107 |
GR | 18TW5 | -14 | -18 | 26371 |
GR | 18TW1 | -13 | -17 | 23420 |
GR | 18TW2 | -12 | -16 | 20384 |
GR | 19TW3 | -11 | -15 | 25985 |
GR | 19TW4 | -10 | -14 | 24766 |
GR | 19TW5 | -9 | -13 | 23288 |
GR | 19TW1 | -8 | -12 | 22067 |
GR | 20TW3 | -7 | -11 | 23991 |
GR | 20TW4 | -6 | -10 | 23145 |
GR | 20TW5 | -5 | -9 | 32189 |
GR | 20TW1 | -4 | -8 | 35398 |
GR | 20TW2 | -3 | -7 | 24983 |
GR | 21TW3 | -2 | -6 | 34249 |
GR | 21TW4 | -1 | -5 | 26249 |
GR | 21TW5 | 0 | -4 | 28968 |
UG | 17EW6 | -24 | -29 | 44849 |
UG | 17EW1 | -23 | -28 | 54718 |
UG | 17EW2 | -22 | -27 | 42965 |
UG | 18EW3 | -21 | -26 | 55702 |
UG | 18EW4 | -20 | -25 | 61369 |
UG | 18EW5 | -19 | -24 | 57039 |
UG | 18EW6 | -18 | -23 | 62101 |
UG | 18EW1 | -17 | -22 | 80622 |
UG | 18EW2 | -16 | -21 | 57543 |
UG | 19EW3 | -15 | -20 | 70901 |
UG | 19EW4 | -14 | -19 | 64923 |
UG | 19EW5 | -13 | -18 | 67889 |
UG | 19EW6 | -12 | -17 | 69548 |
UG | 19EW1 | -11 | -16 | 81491 |
UG | 19EW2 | -10 | -15 | 66914 |
UG | 20EW3 | -9 | -14 | 75910 |
UG | 20EW4 | -8 | -13 | 77401 |
UG | 20EW5 | -7 | -12 | 78476 |
UG | 20EW6 | -6 | -11 | 84550 |
UG | 20EW1 | -5 | -10 | 105522 |
UG | 20EW2 | -4 | -9 | 76644 |
UG | 21EW3 | -3 | -8 | 91358 |
UG | 21EW4 | -2 | -7 | 98226 |
UG | 21EW5 | -1 | -6 | 87241 |
UG | 21EW6 | 0 | -5 | 88605 |
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!
Hi @heidibb
Is this starting looking good!
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |