Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
  • mattbrice's avatar
    mattbrice
    9 years ago

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

      


    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.

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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's avatar
    GershwinMunich
    Regular Visitor
    8 years ago

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

    Can you post the solution please?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      GershwinMunich the solution is stated by mattbrice :)

       

      It's been a while since I worked on this now, but let me know if you can't get it to work.