Forum Discussion
Please help me.
Good Afternoon - I have spent many hours and have no internal bodies to partner with. I have a list of raw data:
I want to be able to show how many customers purchased X amount of times (1 - 25, 25+) and average spent over a given period. The "GERSCustomerCode" is listed multiple times.
So, number ot times purchased on X axis and Number of Customers who purchased those amount of times on Y axes with the ability to slice on date.
Does this make sense? I am great at excel but not so much in PBI.
7 Replies
Hi, Livia_Hernandez
follow the steps in power bi
a. Total Purchases: Count the total number of purchases for each customer.
Total Purchases = COUNTROWS('YourTableName')
Total Customers: Count the total number of unique customers.
Total Customers = DISTINCTCOUNT('YourTableName'[GERSCustomerCode])
Total Sales: Calculate the sum of all the purchase amounts.
Total Sales = SUM('YourTableName'[PurchaseAmount])
Average Spent: Calculate the average amount spent per customer.
Average Spent = DIVIDE([Total Sales], [Total Customers], 0)
Go to the "Report" view to create the visuals.
a. Bar Chart: Drag the "Total Purchases" measure to the Values field well of a bar chart visualization. Then, drag the "GERSCustomerCode" field to the "Axis" field well of the same visualization. This will show the number of times each customer has made purchases.
Thanks! @ rubayatyasmin - I understand what you are suggesting but - this will not solve my issue as I'm dealing with 7 years of data and million plus customers. I need to group them by number of orders. I don't want an average of how much each one spent. I need to know how many customers bought 2 times - 50 times etc. I can't use "GERScustomercode" in anytype of visualization. Make sense?
need to perform some data transformation steps to group the customers by the number of orders they made. Go to the "Transform Data" (Power Query Editor) view.
a. Add Custom Column: Click on "Add Column" in the Power Query Editor. Then, use the following M code to calculate the number of orders for each customer:
= Table.AddColumn(#"PreviousStepName", "NumberOfOrders", each List.Count(Table.SelectRows(#"PreviousStepName", each [GERSCustomerCode] = [GERSCustomerCode])))
emove Duplicates: After adding the custom column, you may have duplicate rows in the table. Remove duplicates based on the "GERSCustomerCode" column, keeping only the "NumberOfOrders" column.
c. Group By: Now, go to the "Home" tab in the Power Query Editor and click on "Group By." Group by the "NumberOfOrders" column and count the number of unique customers in each group.
