Solved: Sum and count customers in different value interva... - Page 2

Anonymous · ‎04-21-2017

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:

Groups	Number of customers	Total sales
<10 000$	123	950 000
10 000$-20 000$	75	1 100 000

Any feedback would be greatly appreciated.

mattbrice · ‎04-21-2017

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.

View solution in original post

Sum and count customers in different value intervals

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly