Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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]))
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 🙂
Solved! Go to Solution.
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
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
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!!! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |