Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I would like to do a count first as the "Year" column has repeating years and then do a cumulative count, is it possible?
hi @Anonymous
can you elaborate your expection with a table?
Hi Freemanz,
Let's say I have a table with the below data,
Column "Year" - has repeating values. With the table below, I would like to do a cumulative count on the 'Year" column.
I am trying to do a ribbon chart that will show me e.g from Year 2017 to Year 2022 the total number of active titles, so the count should be cummulative.
Index | Title | Category | Status | Year |
1 | ABC | A | Active | 2015 |
2 | DEF | A | Active | 2017 |
3 | GHI | B | Active | 2015 |
4 | JFG | B | Active | 2016 |
5 | TIR | C | Active | 2019 |
6 | DEC | C | Active | 2016 |
Somthing like below
Year | ||
2017 | 1 | 1 |
2018 | 2 | 3 |
2019 | 3 | 6 |
2020 | 4 | 10 |
hi @Anonymous
supposing this is your dataset:
Year |
2017 |
2018 |
2018 |
2019 |
2019 |
2019 |
2020 |
2020 |
2020 |
2020 |
try to plot a table visual with the [Date] column and a measure like this:
CountRT =
COUNTROWS(
FILTER(
ALL(TableName),
TableName[Year]<=MAX(TableName[Year])
)
)
it worked like this:
Hi FreemanZ,
I have rows in the Year column that is blank which it is counting 128 blank as the starting number, possible that it doesn't pick up the blank rows ?
hi @Anonymous
you may choose not to show the blank rows in the filter pane.
p.s. consider @ someone, if you expect to continue a discussion.
Hi Freemanz,
It worked! But would it be possible to not filter by all? I would like it to filter by a case status column and only active status?
Thank you.
HI @Anonymous
then change the code to :
CountRT =
COUNTROWS(
FILTER(
ALL(TableName[Year]),
TableName[Year]<=MAX(TableName[Year])
)
)
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
137 | |
114 | |
102 | |
70 | |
56 |