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.
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]),
C_ARR 2 = CALCULATE([NN_ARR],
Where [NN_ARR] = CALCULATE(SUM('Sales'[NN_ARR]))
I attempted a Customer Count measure that produces a result, that is incorrect:
Customer Count =
"Customer C_ARR", SUMX(CURRENTGROUP(),'Sales'[NNARR])
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:
Fact table looks like something like this:
|Cust#||close date||Geo||Product||Net New ARR|
Solved! Go to Solution.
Could you explain the figures in the output table. Take AMS as an example for all 4 years and all products.
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 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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
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!