cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mapko Regular Visitor
Regular Visitor

Complex comparative analysis using DAX

All,

 

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!

 

3 REPLIES 3
AlexChen Member
Member

Re: Complex comparative analysis using DAX

Hi,

 

I assume you have a table like below:

 

1.png

 

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.

 

2.png

Best Regards

Alex

mapko Regular Visitor
Regular Visitor

Re: Complex comparative analysis using DAX

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!

Marko 

AlexChen Member
Member

Re: Complex comparative analysis using DAX

Hi,

 

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

Alex