Running Total with Sparse Array

[Compunuke]

I am trying to create a DAX expression that provides an accurate Running Total over a "sparse array" of data.

The actual dataset in the model has more than 200k rows and over 30 columns.  Here I offer a hypothetical simplified example.

The goal for the hypothetical example is to develop a line chart that shows a growing Running Total over all the weeks in the table even though products don't have sales in every week.

The image below shows the 3 column dataset.  The matrix table shows that sales data exist for products only in certain weeks.
As can be seen, in all cases [table, matrix and line chart], the running total ignores partitioning by Product and cannot carry forward a running total value into a week for which the product had no sales.  In the example, every week had sales for some product. In the real world problem to solve, there should be an RSum value even for a week with no sale of any products.

I have tried the real world case with a calculated dimention table to list every week so that weeks will exist even if the sales data has no row for that week.

In the graphic I am showing a simple DAX formula for running total that is obviously not up to this task.

[parry2k]

@Compunuke this is way easier if someone has followed my original post, by adding calendar dimension, and using that for running total should take care of everything. It is your choice, to write a complex dax or improve the data model and follow the best practice. good luck!

[Compunuke]

I mentioned in the original entry:

"I have tried the real world case with a calculated dimension table to list every week so that weeks will exist even if the sales data has no row for that week."

The "Week" dimension table is basically the "calendar" you are suggesting.  The X-axis has every value from the dimension table whether there are sales in that week or not so there will be no missing numbers on the X-axis or in the calculations dependent on Week#.  However, if there is no quantity sold for a week, there is no calculation. 

[Anonymous]

Hi @Compunuke ,

Please refer to my pbix file to see if it helps you.

Create two measures.

measure = CALCULATE(SUM('Table'[Quality]),FILTER(ALL('Table'),'Table'[week]<=MAX('Table'[week])))

Measure 2 = IF(HASONEVALUE('Table'[week]),[measure],SUMX('Table',[measure]))

If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Compunuke]

Thanks for the effort you put in to this alternative solution.  Unfortunately, it does not solve the aggregation [Running Total] by Product issue.  I have, however, made progress to a better answer, but not quite the solution I seek.

It appears that adding an explicit VALUES element to the expression does force the measure to consider the products.

The running total is now an accurate accumulation for each Product and the RSUM graph is now correct, however, I still cannot figure out how to reflect Running Totals for each product in weeks where there are no sales for the product.  The Running Total should be carried forward from the last week in which there were sales of that product.  Said differently, there shoud, for instance, be a Week 2 and 3 for Product A even though there were no sales of Product A in those weeks.

I hope to solve this in calculations in the measure, not in creating tables.  As I mentioned originally, the actual data this example is based on in already in excess of 200k rows so I'm not looking for a solution that involves adding all the missing rows in the sparse array.

[Compunuke]

Adding model info showing two dimensions and the adjusted Measure DAX.  The visuals are the same as above.

RSum =

CALCULATE(

SUM('Table'[Quantity]),

VALUES('Products'[Product]),

FILTER(

ALLSELECTED('Weeks'),

'Weeks'[Week] <= MAX('Weeks'[Week])

)

)

[parry2k]

@Compunuke when you are working with dates, it is a best practice to add date dimension in your model and then use that to calculate the running total and also use the columns from the date dimension to visualize the data and that will take care of what you are looking for. Check my YT playlist on dates here and the first video talks about the date dimension.

Learn Importance of Date Table and how Time Intelligence DAX functions work - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.