# measuring cross-purchase with purchase category in both row and column

edited June 2021 in Charting

I'm trying to create a table that shows cross-purchase with customers, so what customers who purchased in one value of a dimension also purchased in a different value of the same dimension. Kind of like those old distance charts that have different cities plotted against each other on x and y axis, with the count of customers where they intersect, e.g. 10% of customers who have purchased in category A (row) have also purchased in category B (column) and similar percentages for other combinations . I thought perhaps a pivot table would be a good way to do that, but I noticed that I am only allowed to use a dimension in the row or the column in Domo.

• I imagine there is a way to do this, perhaps better than I'm initially imagining, so I welcome any suggestions.

• Portland, Oregon 🥷

You might try the Heat Map chart. It is useful for things like this. See the example in this KB article:

https://domohelp.domo.com/hc/en-us/articles/360042924634-Heat-Map

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Budapest / Portland, OR 🔴

I've worked on this problem for a client.

You'd have to build a table that does a CROSS APPLY.

So if you have data,

item_id, order_id, amount

you'd do a self join

SELECT

a.*

, b._item_id

from table a

JOIN table b

ON a.orrder_id = b.order_id

and A.item_id < b.item_id

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• thanks jaeW! can you expand on this a bit as I'm struggling to complete this exercise as well? I have all my sales data by customer_id and item_id in one table and I'm trying to do exactly what OP described.

• also to clarify, I'm not trying to show purchases with the same order, just trying to show count of multiple items bought by same customer like the attached picture.

• Budapest / Portland, OR 🔴

@user01109 it's the exact same code / solution. just JOIN on customer instead of order.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• We are unfortunately limited through through a third-party Domo license, which hosts federated data externally, so we don’t get all of the nice data ETL functionality. I’m limited to Beast Mode for data manipulations. I wondered if maybe there’s something I hadn’t considered, but it seems like we would need to build a separate table or have the ability to do Joins.