Compare one with average of multiple

[ace_SB]

Hi Experts,

I am failry new to PowerBI and need some help. I am trying to compare value of one customer to the average of the same value of a multiple customers.

Sample Data:

Year   Customer  ProductPurchased

2016   A               225

2016   B               200

2016   C               25

2016   D              100

2015   A               158

2015   B               150

2015   C               30

2015   D              90

I should be able to pick a Year (Slicer 1) and then pick a customer (Slicer 2) and compare the ProductPurchased by that customer to Average of ProductPurchased for customerS picked in (Slicer 3, say, for e.g. Customers C and D). For average, the customers will be dynamically selected. I was only able to to add a measure to calculate average for all customers, but I need to compare one customer's ProductPurchased with average of multiple customerS (not all). Any help is greatly appreciated. Thanks a lot!

[Zubair_Muhammad]

Hi @ace_SB

First create a New Table (This will be used as Slicer for Competitor Products)

From the Modelling Tab>>NEW Table

Competitors =
ALL ( Table1[Customer] )

Now we can use this MEASURE in your Table (say Table1)

Average Of Competitors =
CALCULATE (
    AVERAGE ( Table1[ProductPurchased] ),
    TREATAS ( VALUES ( 'Competitors'[Customer] ), Table1[Customer] )
)

[Zubair_Muhammad · Accepted solution]

@ace_SB

Please see the attached file