Forum Discussion
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 ...
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.
mattbrice
Solution Sage
Solution SageIf 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.
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.