Power BI DAX query to build a logic for Creating an Overall Average for Suppliers

[ShaGuna32]

Hi Team,

It will be much appreciated if anyone can help me in deriving a logic to calculate the overall Average against each customer by excluding the current line supplier.

Example: 

The Dax should automatically eliminate the current line's Supplier and should calculate the average for the rest of the Suppliers.

Supplier	Sales	Comparative Average
A	3	Average(b&C)
B	5	Average(A&C)
C	4	Average(B&C)

Any suggestion will help in my analysis to calculate the benchmarking against each other. 
Thanks so much for your time,

Kind Regards
Sha

[bolfri · Accepted solution]

Hi,

I think you need that one:

Comparative Average = 
CALCULATE(
    AVERAGE('Sample'[Sales]);
    FILTER(
        ALLSELECTED('Sample');
        NOT('Sample'[Supplier] in VALUES('Sample'[Supplier])))
    )

Basic measures:

Sum of sales = SUM('Sample'[Sales])

Average sales = AVERAGE('Sample'[Sales])

Results:

Note that I've extended the data that you've provided to more rows.

[ShaGuna32]

Hi Bolfri,

Thanks for the Idea, It worked Exactly the way I wanted. Much appreciated.

kind Regards
Sha

[ribisht17]

@ShaGuna32 

Step 1 

Total Sales = CALCULATE(sum('Sales Table'[Sales]),all('Sales Table'))

Step 2

Count of Suppliers = CALCULATE( COUNT('Sales Table'[Suppplier]),all('Sales Table'))-1

Step 3

Final Avg = DIVIDE('Sales Table'[Total Sales]-sum('Sales Table'[Sales]),[Count of Suppliers])

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !! PL 300 Certification Series

[ShaGuna32]

Thanks, Ritesh, for your suggestion.
Much appreciated.

Kind Regards

Sha