Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

cumulative running total inside of a virtual table (Grouby, SUMX, Filter)

Hi All,


I’ve been trying to create a measure that uses the concept of a counting the rows of a virtual table, where the rows are created by assessing if running total (sumx) is greater than 0.


For example,


I have a calendar table and many other mapping tables tied to a fact table of transactional sales data.  The sales data records are for Net New ARR… so to determine if a customer is still an active customer, I sum the cumulative sales to date and assess whether the cumulative ARR is >0… then count those rows to get a customer count… fairly straight forward to do in excel with the associated data model pivot.


I’d like to build out a measure for a matrix viz and dashboard on PBI so that I can easily do customer count trending and avoid some of the manual excel work … and have more flexibility in cutting the data along other characteritics.

I’d like to create a matrix viz with a time period set as columns and various cuts of the data set as the rows…  the number of customers of a particular product, geo, etc. for any determined period of time. See below for example.


I’ve been able to create a 2 different working measures for Cumulative ARR (the running total) of the net new ARR (see below) but have been unable to translate this concept into the customer count measure:









Where [NN_ARR] = CALCULATE(SUM('Sales'[NN_ARR]))



I attempted a Customer Count measure that produces a result, that is incorrect:

Customer Count =




        'Sales'[Customer Number],

        "Customer C_ARR", SUMX(CURRENTGROUP(),'Sales'[NNARR])


    [Customer C_ARR]>0



Somehow I need to bring in the concept of cumulative running total (calendar date<=max(calendar date)) -- but I haven't been able to crack it. 


There are 1m+ transaction lines so the virtual table piece needs to be as efficent as possible. 


Thanks in advance!!




I’d like the output table to look like this:

Customer Count    


Fact table looks like something like this:

Cust#close dateGeoProductNet New ARR
11/1/2019AMSa        1,000,000
11/1/2020AMSa           250,000
11/1/2021AMSa           250,000
11/1/2022AMSa       (1,500,000)
21/1/2019AMSb        1,000,000
21/1/2020AMSb           250,000
21/1/2021AMSb           250,000
21/1/2022AMSb                      -  
31/1/2019AMSb        1,000,000
31/1/2020AMSb           250,000
31/1/2021AMSb           250,000
31/1/2022AMSb                      -  
41/1/2019EMEAa                      -  
41/1/2020EMEAa                      -  
41/1/2021EMEAa        1,000,000
41/1/2022EMEAa                      -  
51/1/2019EMEAb        1,000,000
51/1/2020EMEAb           250,000
51/1/2021EMEAb           250,000
51/1/2022EMEAb                      -  
61/1/2019APACa        1,000,000
61/1/2020APACa           250,000
61/1/2021APACa           250,000
61/1/2022APACa       (1,500,000)


You may download my PBI file from here.

Hope this helps.


Ashish Mathur

View solution in original post

Super User
Super User


Could you explain the figures in the output table. Take AMS as an example for all 4 years and all products. 

Ashish Mathur

I think I may have mislead by saying "output table" ... this is what i want the matrix viz to look like based on the samle data... I've put Geo and Product into the "rows", and "year" into the columns, and the measure that I need help with is what I want to put in the "Values" box. 


Maybe it makes more sense with the formatting... Geography and product are the "Rows" that I'd like to add to the matrix viz so that I can easily look at customer count Trends across various products and geography cuts... as well as other cuts that i didin't show like account size, account industry etc.

The Fact table looks like this... i've shown what i want the virtual table to do to determine if a customer is still active. 



Matrix viz built like this... the Measure "customer count" is what I need help with




You may download my PBI file from here.

Hope this helps.


Ashish Mathur

Ashish this is great -- works nicely and I was able to add/tweak what I needed to get it done!

Thank you for your help!

Quick question -- do you know of a way to do this same thing with a "groupby" and "sumx" construct rather than a "summarize" and "calcualte" construct?  I've got quite a bit of data to pass through this. 

You are welcome.  No, i would not.

Ashish Mathur

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors