Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors