cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
smitre
New Member

Filtering multiple purchases per customer on a given day

Hello, 

 

I'm trying to find a way to create a table or report that allows me to filter my data for a very specific situation. My data set includes customers who have bought and sold stock over a quarter. I need to identify customer's who have purchased or sold any given stock within the same day. 

 

here is a sample format of the data that I have. 

 

CustomerNo | Stock Symbol | Buy/Sell | Order Date | order time
1234              |AAPL               | buy        |6/30/2020   | 9:30:00

Ideally I'd like to have a table or matrix that shows me customers who have bought and sold the same stock on the same day. I can't seem to find a way to do this but I'm also very new to power BI so any help would be greatly appreciated. 

 

2 REPLIES 2
williamadams12
Resolver I
Resolver I

@smitre - I'd start with creating a data model, so focus on the relationships you want to create which would give you the ability to slice and filter on the values you need. In your case, you need common values for the order type, date and customer number. So, think of the orders themselves as one (or maybe two) distinct fact tables and the order dates, order types and customer numbers as dimensions you can connect to the fact table(s). 

 

If you retain all of the data in a single fact table, you'd have to write some DAX measures for buy orders and sell orders separately in order to properly slice/filter the data accordingly, but for the purpose of a simpler answer here just split the order types into two distinct fact tables for buy and sell. 

 

Buy Table: 

williamadams12_0-1656610383371.png

 

Sell Table: 

williamadams12_1-1656610431956.png

 

Connect the Facts to the Dimensions: Date, Customer No, and Order Type:

williamadams12_2-1656610503810.png

 

 

Create Matrix Table: 

williamadams12_3-1656610589880.png

 

You can toggle/filter the order type of buy/sell to count the orders for each customer for each date in the matrix to determine which customers have multiple transactions per day. This is what you wanted, but keep in mind it may worth it to consider the granularity of your date slice and whether you want to incorporate a more robust calendar table, which you can of course drop in as rows or columns. Just depends on what you're trying to analyze and why. 

 

Hope this helps, 

I will give this a shot, thank you so much for getting me started!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors