cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Scitech Regular Visitor
Regular Visitor

Sum and count customers in different value intervals

Hi,

 

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:

GroupsNumber of customersTotal sales
<10 000$123950 000
10 000$-20 000$751 100 000

 

Any feedback would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
mattbrice Senior Member
Senior Member

Re: Sum and count customers in different value intervals

If you create a parameter table with the ranges you want like this:

  tablerange.gif


then with Range Table desciption on rows, use these measures: 

 

Number of Customers =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( CustomerID[ID] ),
            "Total Sales", CALCULATE ( SUM ( Sales[Sales] ) )
        ),
        [Total Sales] >= MIN ( RangeTable[Min] )
            && [Total Sales] < MAX ( RangeTable[Max] )
    )
)

 

Total Sales for Group =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ADDCOLUMNS (
            VALUES ( CustomerID[ID] ),
            "Total Sales", CALCULATE ( SUM ( Sales[Sales] ) )
        ),
        [Total Sales] >= MIN ( RangeTable[Min] )
            && [Total Sales] < MAX ( RangeTable[Max] )
    )
)

 I freehanded these and didn't test them, but they should be close.

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Sum and count customers in different value intervals

Hi @Scitech,

 

You need to do add a column with the following code:

Grouping = SWITCH(
			TRUE(),
			Sales[Sales]<=1000,"<=1.000",
			Sales[Sales]>1000 && Sales[Sales]<=3000,"1.000-3.000",
			Sales[Sales]>3000 && Sales[Sales]<=5000,"3.000-5.000",
			Sales[Sales]>5000,">5.000"
)

Then add this to you visual and select the appropiated summarization for you customers or total sales

 

Regards,

 

Mfelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Scitech Regular Visitor
Regular Visitor

Re: Sum and count customers in different value intervals

@MFelix

 

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?

mattbrice Senior Member
Senior Member

Re: Sum and count customers in different value intervals

If you create a parameter table with the ranges you want like this:

  tablerange.gif


then with Range Table desciption on rows, use these measures: 

 

Number of Customers =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( CustomerID[ID] ),
            "Total Sales", CALCULATE ( SUM ( Sales[Sales] ) )
        ),
        [Total Sales] >= MIN ( RangeTable[Min] )
            && [Total Sales] < MAX ( RangeTable[Max] )
    )
)

 

Total Sales for Group =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ADDCOLUMNS (
            VALUES ( CustomerID[ID] ),
            "Total Sales", CALCULATE ( SUM ( Sales[Sales] ) )
        ),
        [Total Sales] >= MIN ( RangeTable[Min] )
            && [Total Sales] < MAX ( RangeTable[Max] )
    )
)

 I freehanded these and didn't test them, but they should be close.

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: Sum and count customers in different value intervals

Hi @Scitech,

Have you resolved your issue? If you have, please mark a helpful reply as answer. Or it's grateful to share your own solution. Please feel free if you have other question.

Thanks,
Angelia

Scitech Regular Visitor
Regular Visitor

Re: Sum and count customers in different value intervals

@v-huizhn-msft

 

My apologies, I have yet to try the solution @mattbrice suggested, but I'll return with a response as soon as I've tried it. Sorry for lateness of my reply.

My My

Microsoft v-huizhn-msft
Microsoft

Re: Sum and count customers in different value intervals

Hi @Scitech,

Don't need to apology. Any helpful reply is great, the purpose of all is to resolve your issue. Hope you have resolved it, and share good news for other people in this community.

Best Regards,
Angelia

Scitech Regular Visitor
Regular Visitor

Re: Sum and count customers in different value intervals

So I finally got around to trying this, and it worked! 🙂 Thanks again for all replies!

Highlighted
Scitech Regular Visitor
Regular Visitor

Re: Sum and count customers in different value intervals

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 🙂

GershwinMunich Frequent Visitor
Frequent Visitor

Re: Sum and count customers in different value intervals

I have the same issue with mutiple rows for the same customer.

Can you post the solution please?

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,472)