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 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.

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.

11 Replies

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       Thank you so much. You are the star. I have been figuring out it for two days all day. Now, I got it. Thanks a million again. You are the star.

    • 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

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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?