Basket Size Analysis

[Anonymous]

Hi all, appreciate in advance any help that can be offered.  The problem I'm essentially trying to tackle is how in DAX to accomplish the creation of a Basket Size value that I can then summarize my sales metrics by.  I can do this in SQL with Subqueries and Pivots in my sleep, but in DAX, no idea where to even start as my basket size is essentially a function of summing up my units per unique transaction ID and then using that summarized value to explain sales performance by basket size.

My table is a typical retail transaction table and is structured as:

Trans ID-Product SKU-Units Sold-Sales Value

Given this, I'd expect the basket size summary, assuming I want to individually highlight baskets 1-5, and then 6+ for anything bigger as:

Really hoping to do this in DAX so that I can allow merchant teams to see these types of metrics relative to their products, for example, how often does product type X get bought in 1 item baskets vs. 2 item baskets.  Or how often is product type X get bought in a two item basket and it is all two items.  I realize these examples are complications on the initial ask, but this is the direction I'm going, so if I can get help with the first question, I believe I can figure out the rest. 

Thanks everyone!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Ashish, thank you so much!  To make sure I understand, you essentially create a "Buckets" dimension table and then do the calcs to fit the fact table measures within each bucket's dimensions?

Trying this on our actual sales table, the concept works, but execution on a large transaction table (billions of rows), doesn't seem to work.  I believe it's the addcolumns/calculatetable driving the issue as the error mentioned cannot return results of more than 1000000 rows (1 million).  So potentially on a smaller raw table this would work.  Unless I missed something else that would result in this issue.

We've resorted to, for now, creating a group by in the edit query.  It's a first step, but ultimately to answer the further basket questions of how often does my customer buy X and only X and in what basket size quantities, I need something like what you've done.

Thanks again!  I consider this answered.

[Ashish_Mathur]

You are welcome.  Your understanding is correct.  It's the large number of rows which is causing the slowdown for sure.  I cannot seem to suggest an alternative.