DAX help - Calculate 'competition'

[Anonymous]

Hi,

Let's say I have a table that contains: [branch], [sales], [area], [product]. Branches cover a variety of areas that are linked to the sale and many branches can operate in the same areas selling the same product. When I select the branch, area and product(s) using filters I can see the number of sales of that branch. For example:

However, I'd like to see the 'competion'. In other words, for the area that a branch operates and for a specific product all sales no matter the branch. I tried something like: CALCULATE (SUM(sales),ALL(branch)), but that negates the area element and I get very high and wrong figures. 

For example: A1 operates in the areas SW, S and W only. How many bicycles were sold in these areas for all branches? I'd also like to exclude any nulls from the 'area' column in the competition calculations

[v-sihou-msft]

@Anonymous

According to your description, you want to calculate the total sales group on Area and Product. Right?

You can try ALLEXCEPT() in your CALCULATE() to keep Area and Product slicing, like:

=
CALCULATE (
    SUM ( Table[sales] ),
    ALLEXCEPT ( Table, Table[Area], Table[Product] )
)

Regards,

[Anonymous]

Yes, something like that. But this code is giving me all areas.

In the report I have a slicer on the branch and another one on the area. The user should be able to select a branch and see the branch's current figures and the competition's. Then if specific area is selected, the values shown are for that specific area for the selected branch and for all branches. 

Is there a way to show the sum(table[sales]) for all branches for only the selected areas? The slicer on the areas is updated when I select a branch, but no selection is applied automatically.