cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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:

C_ARR = CALCULATE(SUMX(VALUES('Sales'[Close Date]),[NN_ARR]),

FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

C_ARR 2 = CALCULATE([NN_ARR],

FILTER(ALLSELECTED('Calendar'),

'Calendar'[Date]<=MAX('Calendar'[Date])))

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

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

Customer Count =

COUNTAX(

GROUPBY(

'Sales',

'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.

Ben

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

 Customer Count Geo Product 2019 2020 2021 2022 AMS total 3 3 3 2 a 1 1 1 - b 2 2 2 2 EMEA total 1 1 2 2 a - - 1 1 b 1 1 1 1 APAC total 1 1 1 - a 1 1 1 - b - - - -

Fact table looks like something like this:

 Table Cust# close date Geo Product Net New ARR 1 1/1/2019 AMS a 1,000,000 1 1/1/2020 AMS a 250,000 1 1/1/2021 AMS a 250,000 1 1/1/2022 AMS a (1,500,000) 2 1/1/2019 AMS b 1,000,000 2 1/1/2020 AMS b 250,000 2 1/1/2021 AMS b 250,000 2 1/1/2022 AMS b - 3 1/1/2019 AMS b 1,000,000 3 1/1/2020 AMS b 250,000 3 1/1/2021 AMS b 250,000 3 1/1/2022 AMS b - 4 1/1/2019 EMEA a - 4 1/1/2020 EMEA a - 4 1/1/2021 EMEA a 1,000,000 4 1/1/2022 EMEA a - 5 1/1/2019 EMEA b 1,000,000 5 1/1/2020 EMEA b 250,000 5 1/1/2021 EMEA b 250,000 5 1/1/2022 EMEA b - 6 1/1/2019 APAC a 1,000,000 6 1/1/2020 APAC a 250,000 6 1/1/2021 APAC a 250,000 6 1/1/2022 APAC a (1,500,000)
1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

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

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.

Super User

You are welcome.  No, i would not.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors