Forum Discussion
DAX percentages
I'm relatively new to DAX and stuck on a particular problem. I would like to build a line chart that shows the percentage of market share of all my products for each month. I have include filters on the page for Country, Divison, Product Line and Month.
I need to calculate the percentage of market share by dividing (revenue/market size); however I do not know how to add that to the whole formula with the filters. (The whole market are the three countries)
It would need to dynamically recalculate with the context of the filter so that only data after the filter is included. And when no filter is selected then I have the total market share of our market.
I have tried various CALCULATE, SUMX, Calculated Table functions with little success.
Sample data is below and any help is very much appreciated, I know tha market share column should be calculated in power bi
Thank YOU
| Country | Division | Product Line | Market Size | Revenue | Market Share |
| Mexico | Dental | Red | 100.00 | 50.00 | 50% |
| Mexico | Dental | Green | 95.00 | 45.00 | 47% |
| Mexico | Cosmetics | Yellow | 125.00 | 100.00 | 80% |
| Mexico | Cosmetics | Pink | 70.00 | 30.00 | 42% |
| Argentina | Dental | Red | 50.00 | 10.00 | 20% |
| Argentina | Dental | Green | 45.00 | 5.00 | 11% |
| Argentina | Cosmetics | Yellow | 80.00 | 45.00 | 56% |
| Argentina | Cosmetics | Pink | 70.00 | 5.00 | 7% |
| USA | Dental | Red | 200.00 | 150.00 | 75% |
| USA | Dental | Green | 130.00 | 85.00 | 65% |
| USA | Cosmetics | Yellow | 250.00 | 200.00 | 80% |
| USA | Cosmetics | Pink | 140.00 | 100.00 | 71% |
2 Replies
- tamerj1
Community Champion
Hi Anonymous
For the denominator you can use something like
CALCULATE ( SUM ( 'Table'[Revenue] ), REMOVEFILTERS ( ) )
Hi Anonymous ,
According to your statement, I think there should lost [Month] column in your sample data. And I need to know the calculate logic about Revenue and Market Size. Will you determind the Market Size each month in your table? And will you need to sum the revenue each month? I think you can try ALLEXCEPT FUNCTION if you want your measure can be filtered by Filter or Slicers.
Please share a sample file without sensitive data with us and show me the result you want. This will make me easier to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
