Comparing 2 sets of data based on slicer selections

[mplotegher]

Hi community, need some help to find a solution to my challenge.

Have a table which lists all customers of a territory and also how much each distributor sold to each of the customers in a particular timeline.

Expected outcome: for any multi-selection of 2 or more distributors, a NEW MEASURE (%) will tells me what is the percentage of customers where all the selected distributors reported any sales in the timeline.

Example: considering the table below, if i wanted to compare Dist A and Dist B, the result will be that both reported sales together to only 2 customers (1 and 7) out of the 7, so Measure will be: 28.6%
If selected Dist B and C, would be 3 customers (1, 2 and 6), out of the 7, so 42.9%.

Cust	Sales Dist A	Sales Dist B	Sales Dist C
1	12	4	88
2	0	55	6
3	78	0	32
4	0	56	0
5	8	0	36
6	0	98	93
7	36	90	0

Any idea on how I could translate that into Power BI?

Thanks!

[anmolmalviya05 · Accepted solution]

Hi @mplotegher, 

Firsly Create a Distributors Slicer

• Ensure that you have a Distributors table with a list of distributors (Dist A, Dist B, Dist C).
• Add a slicer for distributors.

Then Create the below measure

Percentage_Customers_Selected_Dist =
VAR SelectedDists = VALUES('Distributors'[Distributor]) -- Get selected distributors
VAR TotalCustomers = DISTINCTCOUNT('SalesTable'[Cust]) -- Count total unique customers

VAR CustomersWithAllSales =
CALCULATE(
DISTINCTCOUNT('SalesTable'[Cust]),
FILTER(
'SalesTable',
SUMX(
SelectedDists,
LOOKUPVALUE(
'SalesTable'[Sales],
'SalesTable'[Distributor], [Distributor],
'SalesTable'[Cust], 'SalesTable'[Cust]
)
) > 0 -- Ensure all selected distributors have sales
)
)

RETURN
DIVIDE(CustomersWithAllSales, TotalCustomers, 0)

After it, 

• Add the measure to a card visual to display the percentage.
• Use the slicer to select multiple distributors dynamically.