cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JoseGo Frequent Visitor
Frequent Visitor

User select Filter in Calculate

Hello everyone,

 

I have been trying to create a table where it shows different cities and a company's market share in each city.

This is an example of the database I am working with:

 

Database.JPG

 

So far I have been able to find out how to calculate the market share for a specific company in each city. Below is my DAX formula for the calculated measure and the resulting table:

Company A %Share = (CALCULATE(SUM(Table1[sales]); Table1[Company] = "A" ) / sum(Table1[Sales]))

 

 Resulting table.JPG

The problem is that I haven't been able to find a way to allow users to choose the Company they are seeing (ex: show the market share for company B instead of A) without having to manually change the formula. I suppose this might involve using the VAR function, however I haven't found a way to associate a Variable to something such as a slicer.

Also the table should adjust if I apply a filter through clicking on another graph, for example.

 

Is there any way to solve or bypass this limitation?

 

Thank you for your time Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: User select Filter in Calculate

does something like this solve the issue?

Company Share = 
VAR SalesCompany = SUM('Table'[Sales])
VAR SalesAllCompanies = CALCULATE(SUM('Table'[Sales]),ALL('Table'[Company]))
RETURN
DIVIDE(SalesCompany,SalesAllCompanies)

you can filter the companies with slicer this way

2 REPLIES 2
Super User
Super User

Re: User select Filter in Calculate

does something like this solve the issue?

Company Share = 
VAR SalesCompany = SUM('Table'[Sales])
VAR SalesAllCompanies = CALCULATE(SUM('Table'[Sales]),ALL('Table'[Company]))
RETURN
DIVIDE(SalesCompany,SalesAllCompanies)

you can filter the companies with slicer this way

JoseGo Frequent Visitor
Frequent Visitor

Re: User select Filter in Calculate

Thank you so, so much, it works perfectly.

 

I will have to change it a little bit for use in ArcGIS maps, however. With my previous DAX formula the various cities still appeared even if the selected company didn't had any sales (showed general market volume though size but with no color which was was associated with the market share), with this new formula you shared that does not occur, it only shows cities with at least 1 sale.

I am trying to use IF funtions to solve this problem but haven't been successful yet.

 

Regardless of this thank you very much for your help!!! Smiley Happy