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

[Anonymous]

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. 

Thanks in advance!!

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)

[Ashish_Mathur]

Hi,

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

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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. 

[Ashish_Mathur]

You are welcome.  No, i would not.