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
Anonymous
Not applicable

How can I do cumulative count on a Year column?

michgeo_0-1675239398972.png

I would like to do a count first as the "Year" column has repeating years and then do a cumulative count, is it possible?

7 REPLIES 7
FreemanZ
Super User
Super User

hi @Anonymous 

can you elaborate your expection with a table?

Anonymous
Not applicable

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.

 

IndexTitleCategoryStatusYear
1ABCAActive2015
2DEFAActive2017
3GHIBActive2015
4JFGBActive2016
5TIRCActive2019 
6DECCActive2016

 

Somthing like below

Year  
201711
201823
201936
2020410

 

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:

FreemanZ_0-1675258029843.png

 

Anonymous
Not applicable

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 ?

michgeo_0-1675260538500.png

 

hi @Anonymous 

 

you may choose not to show the blank rows in the filter pane. 

FreemanZ_0-1675317729393.png

 

p.s. consider @ someone, if you expect to continue a discussion. 

Anonymous
Not applicable

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])
    )
)

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.