cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

1 ACCEPTED SOLUTION
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

4 REPLIES 4
Super User

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper I

Hi Bolfri,

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

kind Regards
Sha

Super User

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
Helper I

Thanks, Ritesh, for your suggestion.
Much appreciated.

Kind Regards

Sha

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors