Forum Discussion
Sales range by customer
Hi,
My problem is quite straightforward and I haven't been able to find a working solution to this. I want to create a slider slicer that allows the users to select the range of sales they want to see. The problem is that my table is by customer and if one customer has many sales rows in the data, the slicer still filters each row.
Here's the sample data I'm working with:
| Customer | Sales |
| Customer A | 100 000 |
| Customer A | 50 000 |
| Customer B | 25 000 |
| Customer B | 10 000 |
| Customer C | 40 000 |
| Customer C | 18 000 |
If I apply a range of 20 000 - 50 000, the slicer will filter out all the values outside of the range (10 000, 18 000 & 100 000). Instead, I want the slicer to adjust the range of the sum of sales for each customer.
Hi Lazuriii,
I'd like to suggest you create a disconnected table as source of slicer, then you can write a measure formula to check the aggregated value and return flag.
flag = VAR selection = ALLSELECTED ( NewTable[Value] ) VAR currSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED ( Table1 ), VALUES ( Table1[Customer] ) ) RETURN IF ( currSales >= MINX ( selection, [Value] ) && currSales <= MAXX ( selection, [Value] ), "Y", "N" )After these steps, you can use this measure formula on the table visual 'visual level filter' to filter records that match with flag.
Regards,
Xiaoxin Sheng
Hi,
I have solved a similar problem in the attached file.
Hope this helps.
4 Replies
If I apply the range of 20 000 - 50 000, the sum table looks as following:
Customer Sales Customer A 50 000 Customer B 25 000 Customer C 40 000 I want it to apply the 20 000 - 50 000 range to the sum of all sales per customer. Here's the wished outcome:
Customer Sales Customer B 35 000 So, since Customer A and C have higher sales than 50 000, they shouldn't be shown in the table at all.
- Ashish_Mathur
Super User
Hi,
I have solved a similar problem in the attached file.
Hope this helps.
Hi Lazuriii,
I'd like to suggest you create a disconnected table as source of slicer, then you can write a measure formula to check the aggregated value and return flag.
flag = VAR selection = ALLSELECTED ( NewTable[Value] ) VAR currSales = CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED ( Table1 ), VALUES ( Table1[Customer] ) ) RETURN IF ( currSales >= MINX ( selection, [Value] ) && currSales <= MAXX ( selection, [Value] ), "Y", "N" )After these steps, you can use this measure formula on the table visual 'visual level filter' to filter records that match with flag.
Regards,
Xiaoxin Sheng
