Forum Discussion
Filter without losing data across other columns
Hi,
Newer PBI user here. I am trying to filter a table by quantity sold of Product A, while not losing the quantity sold of Product B. The source table contains one product per order per line along with salesperson and quantity sold. There can be multiple lines with the same product and salesperson.
Ex. Source table:
| Salesperson | Product ID | Quantity Sold |
| Duke | ABC88 | 1 |
| Duke | ABC88 | 2 |
| Duke | DEF99 | 1 |
| Ramses | DEF99 | 1 |
| Ramses | JKL22 | 1 |
| Ramses | ABC88 | 2 |
| Lara | ABC88 | 1 |
| Lara | JKL22 | 2 |
Example - summary table visual with no filters:
| Salesperson | Qty Sold ABC88 | Qty Sold DEF99 | Qty Sold JKL22 |
| Duke | 3 | 1 | 0 |
| Ramses | 2 | 1 | 1 |
| Lara | 1 | 0 | 2 |
I need to be able to determine 1) Who has sold ABC88 but hasn't sold DEF99, and 2) For everyone who sold ABC88, how many JKL22 did they sell? Results-wise, on #1 I only want to see the line for Lara. For results on #2 I want to see that Duke sold 3 ABC88 and 0 JKL22, Ramses sold 2 and 1 respectively, and Lara sold 1 and 2 respectively.
For both questions, if I filter by Qty Sold ABC88 > 0, then because of how the source table is set up with one product per line, I only end up with the lines with ABC88 and thus lose my other product quantities.
How can I get around this so even if I filter the summary visual by one product, I can still see the counts of the other products for that salesperson? Thanks for your consideration.
I ended up discovering the SUMMARIZE function and consolidating the table down to one line per salesperson. That was easier to work with than the multiple rows.
5 Replies
- Ashish_Mathur
Super User
Hi,
Your first question should get answered by my solution in the attached file.
Hope this helps.
Thank you! I ended up consolidating the table (which I wasn't originally planning to do and didn't ask about) because that was easier to work with. I still appreciate your response because I learned about a couple new functions I didn't know about (ex. REMOVEFILTERS).
- DataNinja777
Hi snarfblatt
You can achive your required output in many different ways and below is an example.
I attach a pbix file.
Thank you! I ended up consolidating the table (which I wasn't originally planning to do and didn't ask about) because that was easier to work with. I still appreciate your response because I learned about a couple new functions I didn't know about (ex. DISTINCT).
I ended up discovering the SUMMARIZE function and consolidating the table down to one line per salesperson. That was easier to work with than the multiple rows.
