Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Please help!
I am working on a dashboard and it was requested that I provide a tab that lists the total sales of a customer (in both of our market segments) as well as providing a list of customers that we have in our system that has not purchased anything from us, or those that have purchased from us, but not for a long time. This way, our salespeople know who to target. I am running into issues when I tried to complete this myself.
Here's an example:
Customer 0123: Purchase 1 for $2,000 on Jan 18 2017 from Market 1. Purchase 2 for $10,000 on Feb 22, 2018 from Market 2. Purchase 3 for $20,000 on Sept 25, 2019 from Market 1. Desired Result: Total Sales: $32,000 - Last Sell Date - Sept 25, 2019. Total Market 1 Sales: $22,000 - Last Sell Date: Sept 25, 2019. Total Market 2 Sales: $10,000 - Last Sell Date: Feb 22, 2018.
Customer 4567: Purchase 1 for $2,000 on Jan 18 2008 from Market 2. Purchase 2 for $10,000 on Feb 22, 2009 from Market 1. Purchase 3 for $20,000 on Sept 25, 2010 from Market 2. Desired Result: Total Sales: $32,000 - Last Sell Date - Sept 25, 2010. Total Market 1 Sales: $10,000 - Last Sell Date: Feb 22, 2009. Total Market 2 Sales: $22,000 - Last Sell Date: Sept 25, 2010.
Customer 8910: No purchase record.
I have a column for the date when a sale was made, sales dollars for both market segments, and a sale column for each market segment. I also have a column for customers. I think I have all the data there, I just need to figure out how to manipulate it to get my desired result.
Please help!
Thanks in advance!
Solved! Go to Solution.
There can be multiple ways to do this. One of the ways I can think of summarizes and union
Union data from Customer data and sales data
Add up = union(SUMMARIZE(Sales,Sales[Customer],"Col1",sum(Sales[Sales]),"Col2",Max(Sales[Date]),"Countfor dim",0),SUMMARIZE(Customer ,Customer[Customer],"Col1",0,"Col2",blank(),"Countfor Dim",COUNTROWS(Customer)) )
You Now on this table you can do the calculation to get when the last sales done , Does customer have sales etc.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
There can be multiple ways to do this. One of the ways I can think of summarizes and union
Union data from Customer data and sales data
Add up = union(SUMMARIZE(Sales,Sales[Customer],"Col1",sum(Sales[Sales]),"Col2",Max(Sales[Date]),"Countfor dim",0),SUMMARIZE(Customer ,Customer[Customer],"Col1",0,"Col2",blank(),"Countfor Dim",COUNTROWS(Customer)) )
You Now on this table you can do the calculation to get when the last sales done , Does customer have sales etc.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
That worked! Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |