Filter without losing data across other columns

[snarfblatt]

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.

[snarfblatt · Accepted solution]

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. 

[Ashish_Mathur]

Hi,

Your first question should get answered by my solution in the attached file.

Hope this helps.

[snarfblatt]

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.  

[snarfblatt]

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).