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 wherebytable 1is showing sales (sum of sales) information related to aselectedcompanyandtable 2is showing sales (average of sales) information tocompaniesthat are within the same industrybutexcludingtheselected companyfor 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
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:
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?