Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Complex comparative analysis using DAX



I am completely new to PBI and have a challenge to resolve. Let's assume I have the following data set:

Column 1: Company name

Column 2: Sales

Column 3: Industry

Let's also assume I have 10 companies within 2 industries. Rather simple data set.


I need to create 2 reports whereby table 1 is showing sales (sum of sales) information related to a selected company and table 2 is showing sales (average of sales) information to companies that are within the same industry but excluding the selected company for table 1 analysis. Also, I would like to have the ability to manually exclude remaining companies from the average calculation (table 2) by deselecting them from a filter object.


For example: (assuming companies 1 - 5 are in the same industry)

Selected company: 1

Table 1 shows sum of sales for company 1

Table 2 shows average of sales for companies 2 - 5

If I deselect company 4 from the industry peer group then table 2 should show average of sales for companies 2, 3, and 5


Any help will be appreciated!





I assume you have a table like below:




1.  Create a measure to get sum of sales in  the selected industry excluding the selected company.


allExceptSum = CALCULATE(sum(companySales[sales]),ALLEXCEPT(companySales,companySales[industry]))-CALCULATE(SUM(companySales[sales]),ALLSELECTED(companySales[company name]))


2. Create a measure to get count of company in the selected industry excluding the selected company.


allExceptCount = CALCULATE(distinctcount(companySales[company name]),ALLEXCEPT(companySales,companySales[industry]))-CALCULATE(DISTINCTCOUNT(companySales[company name]),ALLSELECTED(companySales[company name]))


3. Create a measure to get average sales in the selected industry excluding the selected compay.


allExceptAverage = [allExceptSum]/[allExceptCount]


4. Now you can create  2 visuals to show them.



Best Regards


Hi Alex,


Thank you for your message and for taking the time to help me out. I really do appreciate it! Your solution definitely helped. I made one adjustment to the allExceptSum measure. Now the measure is calculating correctly for related companies:


allExceptSum2 = CALCULATE(sum(Table1[Sales]),ALLEXCEPT(Table1,Table1[Industry]))-CALCULATE(SUM(Table1[Sales]),ALLSELECTED(Table1[Company]))


One remaining question is how can I further manually deselect specific companies from this measure's calcuation? Filter object reduces records acutomatically. So example, if I select Comp1 to do sum of sales then allExceptSum2 will calcuatate sum of Comp2, 3, 4, and 5. How can I deselect Comp5 for this?


Again, thank you for taking the time!




If you want to disselect comp5, you can minus the sum of sales from allExceptSum:


allExceptSum = CALCULATE(sum(companySales[sales]),ALLEXCEPT(companySales,companySales[industry]))-CALCULATE(SUM(companySales[sales]),ALLSELECTED(companySales[company name]))-CALCULATE(sum(companySales[sales]), companySales[company name] = "comp5")


Best Regards


Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors