Forum Discussion
Running total by binned count
OK, yes, I have searced for this. My search expertise is clearly lacking but that's life sometimes.
I have a table like this:
| Bin | Bin Min | Bin Max | Count | What I Want - Cumulative Count | Try 1 | Try 2 |
| 0 | 1 | 24 | 19868 | 19868 | 35504 | 705393472 |
| 25 | 25 | 49 | 11115 | 30983 | 49932 | 554994180 |
| 50 | 50 | 74 | 4111 | 35094 | 54382 | 223564402 |
| 75 | 75 | 99 | 1702 | 36796 | 56138 | 95546876 |
My formula for try1:
COUNTROWS(filter(all('Table'),'Table'[E (Bins)]<=max('Table'[E (Bins)])))
My formula for try 2:
sumx(filter(all('Table'),'Table'[E (Bins)]<=max('Table'[E (Bins)])),DISTINCTCOUNT('Table'[FieldC]))
I've got to be close because I've done this on other non-binned things and it's been fine but on this, I just don't know. I feel like I may be trying to do a sum when what I really need is a count but neither works for me.
I am also mystified as to why the total on try two decreases but that may be a problem for another post.
I'm looking forward to y'all's assistance!
2 Replies
- Greg_Deckler
Community Champion
tdhughes Is that how your source data looks?
Data Source looks like this:
Year Y2 C_ID C E E (bins) 2021-22 202109 2.02E+10 80532 1 0 2021-22 202109 2.02E+10 81083 1 0 2021-22 202109 2.02E+10 86090 1 0 2021-22 202109 2.02E+10 86472 1 0 2021-22 202109 2.02E+10 86592 1 0 E is an integer ranging from 1 to 4,315, bins are created using the binning function and are on a 25 increment basis. The count and cumulative counts are based on a distinct count of the "C" value - so the data shown here would yield a count of 5 for bin 0.
