Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Basket Size Analysis

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


2019-04-30 15_45_30-Book1 - Excel.png

 

 

 

 

 

 

 

 

 

 

 

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:


2019-04-30 16_01_41-Book1 - Excel.png

 

 

 

 

 

 

 

 

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!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.