topic Re: DAX percentages in DAX Commands and Tips

DAX percentages

Anonymous — Tue, 26 Apr 2022 21:51:32 GMT

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%

Re: DAX percentages

tamerj1 — Wed, 27 Apr 2022 13:00:52 GMT

Hi Anonymous

For the denominator you can use something like

CALCULATE ( SUM ( 'Table'[Revenue] ), REMOVEFILTERS ( ) )

Re: DAX percentages

Anonymous — Wed, 04 May 2022 06:58:39 GMT

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.