cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX help - Calculate 'competition'

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:

 

Competition example.PNG

 

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

2 REPLIES 2
v-sihou-msft
Microsoft
Microsoft

@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
Not applicable

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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.