Forum Discussion
Dynamically filter a table to perform benchmarking
Hi all. I've been stewing on this all day and can't figure it out. I have two tables:
1) A list of salesperson names and office locations:
Salesperson in Table 1 has an established one:many relationship with Salesperson in Table 2, and Table 1 is set to filter Table 2
USERS TABLE
| Salesperson | Office | Region |
| Jon Snow | Wall | North |
| Cersei Lannister | Westeros | Mid |
| Sansa Stark | Winterfell | North |
| Samwell Tarly | Wall | North |
2) A list of salesperson names, customer names, offices, and sales amounts:
SALES TABLE
| Salesperson | Customer | Sale | Office |
| Jon Snow | B | $4,500 | Wall |
| Jon Snow | C | $5,000 | Wall |
| Jon Snow | B | $650 | Wall |
| Jon Snow | B | $500 | Wall |
| Sansa Stark | D | $67,000 | Winterfell |
| Sansa Stark | D | $1,200 | Winterfell |
| Sansa Stark | D | $17,000 | Winterfell |
| Sansa Stark | E | $12 | Winterfell |
| Samwell Tarly | F | $100,000 | Wall |
| Samwell Tarly | F | $99,000 | Wall |
| Cersei Lannister | G | $17 | Westeros |
I have a report where the user selects a salesperson from the slicer, and we show the sales amount across customers for that salesperson. Pretty standard stuff.
What I also want to do is to show the sales amount for other employees with the same office location or region, as a benchmark. So you'd see something like the table below, where you can see that Jon Snow has lower-than-average sales as compared to other salespeople at the Wall or in the North.
| Salesperson | Total Sales | Average Sales |
| Jon Snow | $10,650 | $2,662 |
| Wall salespeople | $209,650 | $34,941 |
| North salespeople | $294,862 | $29,486 |
I'm pretty sure my answer lies in a Calculated table, but whenever I build one, I end up with all the expected columns, except they're empty.
I've been using variations of the below measure to create the virtual table, but no matter what I try, I get sales = blank. Thank you in advance for any help you can provide!
1 Reply
Anonymous , try measure like
Measure =
var _office = Summarize(allselected(Table1), Table1[Office])
var _Region = Summarize(allselected(Table1), Table1[Region])
return
calculate(averageX(Values(Table1[Salesperson]), calculate(Sum(Table2[Sale]))), filter(all(Table1),Table1[Office] in _office && Table1[Region] in _Region))
