How to get a monthly cumulative total on non-numeric data?

Anonymous — Thu, 20 Oct 2022 08:50:59 GMT

Hi there,

Relatively new to Power BI, but getting complex fast as I'm having to deal with millions of records!

My issue is I want to generate a cumulative calculation of data that is not numerical. I'e I want to count incidences of things, not add up values:

I have connection data for a range of devices, rented by different operators (in the columns here) in each month, operators connect a number of new devices, but rather than a single total being recorded, a record of each individual device with owner attributes, installation date etc is held. Each of 3 million rows represents a single device. As you can see, I can calculate the number of devices each operator connects each month, by counting the incidences of their name being attched to units in that month, but I then want to generate cumulative totals; i.e how many devices they are operating in total as time passes.

So in the case above I'd like to genrate a column that displayed the running total for EOD as 7, 10, 11, 342, etc rather than 7, 3 ,1 , 331; as it is now. Ideally this would continue through the years, so EOD in January 2022 would be 3930 (3883+47) not 47.

I've already tried forms of :

Running Total = CALCULATE(Sum('qry\'[Sample]),FILTER(ALL('qry\'[Delivered]),'qryS\'[Delivered] <= Max('qry\'[Delivered])))

but I cannot seem to get them to do what I want.

Can anyone make this work without fusing my mind?!

Thanks in advance

AKI

Re: How to get a monthly cumulative total on non-numeric data?

tamerj1 — Thu, 20 Oct 2022 09:03:19 GMT

Hi Anonymous
Is [Sample] a column? What does it contain? Apparently you're not using a Date Table?

Re: How to get a monthly cumulative total on non-numeric data?

Anonymous — Thu, 20 Oct 2022 09:30:51 GMT

So these are the data fields and parameters for the base report. I cannot modify these as such, but have built some additional columns (not required for what I'm after) to do some secondary cross-referencing.

topic How to get a monthly cumulative total on non-numeric data? in DAX Commands and Tips

How to get a monthly cumulative total on non-numeric data?

Re: How to get a monthly cumulative total on non-numeric data?

Re: How to get a monthly cumulative total on non-numeric data?