I've got a datasets with alot of rows for different customer numbers and sales value. I want to sum the sales value on each customer and then show the number of customers and the total sales value in different categories.
For example, I want to show the total value of all the customers that has a sales value < 10 000$. Then another group for between 10 000$ - 20 000$, and so on.
I might need to work on the description of my problem, but this is basicly what I want it to look like:
Thanks for your reply. Your solution worked on a test dataset I have where I only have 1 row per customer. However, my real dataset contains multiple rows for the same customer (individual transactions/sales), and this is where I want to summarize the total sales value for each customer, and then group these under the categories mentioned above. Can this be done?
Now, this is only nitpicking on my part, but I'm asking anyway since you managed to give me a solution the last time:
As I said, the solution @mattbrice supplied works perfectly, but there's small detail that would be nifty. My sales data per customer is connected to a dimCustomer-table, which stores customer information. In the same report where I made this category/range-analysis, I've also got a couple of graphs showing the largest customers etc (bar-charts). When I click on the bar for the largest customer, the range-analysis (expressed by donut-chart) changes since that effectivly puts a filter on the customer-ID. However, it would be nice if I could go the other way aswell, meaning if I click on a range (say <10 000) in the donut chart, the "largest customer" bar-chart would change correspondingly, only showing the customers in that perticular range.
Is this possible without connecting the new range-table to the dimCustomer table? Again, I'm perfectly happy with the solution supplied so far, so I'm just wondering if this is possible without going through to much trouble 🙂