- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2016 04:24 AM

The answer based on my calculations for VanArsdel's market share is incorrect:

Here are my calculations:

Total VanArsdel Revenue = CALCULATE(SUM(Sales[Revenue]),Manufacturers[Manufacturer]="VanArsdel")

Total VanArsdel Revenue = 2276898965.01 (This is correct)

As per the DAX expression hint in the lab course i need to use the following query:

% Units Market Share = IF([Total VanArsdel Revenue]=0,0,DIVIDE([Total VanArsdel Revenue],[Total Revenue],0))

The value based on my query is: 0.44

Any hint to solve the query would be of help :-)

Solved! Go to Solution.

Accepted Solutions

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2016 08:54 AM - edited 06-19-2016 08:57 AM

Did you format the measure as percentage? If not, it will be a decimal value and you would need to multiply your answer * 100 to get the percentage.

Thanks,

Sam Lester (MSFT)

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2016 03:03 AM

I have found the problem: since I have to use semi colon instead of commas, I also needed to use a ";" in between the two "0":

% Sales Market Share = IF([Total VanArsdel Sales]=0**;**0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

All Replies

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2016 08:54 AM - edited 06-19-2016 08:57 AM

Did you format the measure as percentage? If not, it will be a decimal value and you would need to multiply your answer * 100 to get the percentage.

Thanks,

Sam Lester (MSFT)

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2016 02:25 PM

For some reason, when I put the formula it doesn't give me any result.

This what I've put:

% Sales Market Share = IF([Total VanArsdel Sales]=0,0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

Why doesn't it give me ANY result? Not even "0". Also, in my Power BI Desktop, it turns the "," into ";". I am living in Germany, might that be a reason?

Can anyone help, please?

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-24-2016 03:03 AM

I have found the problem: since I have to use semi colon instead of commas, I also needed to use a ";" in between the two "0":

% Sales Market Share = IF([Total VanArsdel Sales]=0**;**0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-01-2017 12:40 AM

*This solution has already been solved above. With this post I'd like to share some insight into the Programming Logic behind the problem and the solution.***Problem Description**

Having entered the formula

% Sales Market Share = IF([Total VanArsdel Sales]=0,0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

And formated this measure as Percentage nothing is shown (Blank() Value).

Most likely this error will occur to people in Europe or other countries that use ; instead of , to seperate parts of the syntax.

The error is caused by how the ; is used in the IF Statement.

DAX IF Statement logic as it relates to this problem.

IF(

[Total VanArsdel Sales]=0,0;

DIVIDE([Total VanArsdel Sales];

[Total Sales];0

))

Here I interpreted the [Total VanArsdel Sales]=0,0; as checking IF Value is 0,0

then perform the Divide.

However when studying the solution found on Power BI Community https://community.powerbi.com/t5/EdX-Specific-Training-Discussion/Power-BI-Desktop-Modelling-gt-Lab-... I deducted that this of course is wrong.

Because we check IF the Sales Value is 0,0 then we use the divide function to avoid division by zero errors. Going back to the IF Logic we are saying If the Sales value is 0 Do this, however we haven't stated an Else Case.

**Solution Logic**

IF(

[Total VanArsdel Sales]=0;

0;

DIVIDE([Total VanArsdel Sales];

[Total Sales];0

))

Now logically what we really want to do with this measure is to apply the DIVIDE function on the value whenever we encounter a 0 to avoid division by 0 error. Otherwise we want to use the normal values.

Therefor the wanted logic is

IF

Expression (Value = 0)

Result if TRUE (Value = 0)

Result if FALSE (Use the DIVIDE Function)

If I understand things correctly then the Division by Zero error occurs when we try to divide

a non 0 value with 0. This only applies when we have a non-zero value so we run the divide

only when we don't have a 0 value for performance reasons.

**Solution DAX Formula**

% Sales Market Share = IF([Total VanArsdel Sales]=0;0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-01-2017 10:31 AM

If you see the hint given for calculation : IF([Total VanArsdel Units]=0,0,DIVIDE([Total VanArsdel Units],[Total Units],0)) '

here, if Total VanArsdel Units=0 then 0 else divide ()

where syntax denotes as given below :

DIVIDE(numerator, denominator [,alternateresult])

if you see clearly the later 3 are all content of the divide() where 0 is the alternate result

moreover, to get a good view for the same refer to the linkhttps://msdn.microsoft.com/en-us/library/jj677276.aspx

Hope you are formatting the measure created as been asked in the question.

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-31-2017 12:12 PM

The DAX expression that you have been using to calculate Total VanArsdel Revenue is incorrect, you don't have to use SUM in the expression and also the correct column to use is "Total Sales" not "Revenue". Replace revenue with total sales and remove the sum, you should be good.

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2017 11:44 PM

% Sales Market Share = IF([Total VanArsdel Sales]=0;0;DIVIDE([Total VanArsdel Sales];[Total Sales];0))

How I see it, the IF statement is useless. Is not filtering a 0 division. It has no sense returning "0" for Total VanArsden Sales, because division do it by itself "0/n" is "0". I did simple division: % Sales Market Share =DIVIDE([Total VanArsdel Sales];[Total Sales];0)

Regards!

## Power BI - Market Share Calculation with different variables

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-23-2018 12:11 AM

Hi All,

I am scratching my head on how to calculate **Market Share with quite a few different variables in my master data** :

- Master Data is very clean but is a combination of Total Industry Volume, by Competitor, Category, SKU, Quarter, **Month**, **Week** etc ...

- Basically, I want to be able the DAX formula to understand that Share of Market is the SUM of ONE SKU VOLUME of a given Week or Month or Quarter DATA POINT (whatever I select), vs. the total INDUSTRY of that very Week or Month or Quarter. Which would then help me look at it/slice and dice by Category, Competitor etc ...

How do I do this ? I do not think it's complicated but percent of total obviously does not work.

Please help out !

Cheers,

Pierre

## Re: Power BI Desktop Modelling > Lab 2 > Exercise 4: Market Share

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-13-2018 02:51 PM

Total VanArsdel Sales = CALCULATE([Total Sales],Manufacturers[Manufacturer]="VanArsdel")

% Sales Market Share = IF([Total VanArsdel Sales]=0,0,DIVIDE([Total VanArsdel Sales],[Total Sales],0))

Total VanArsdel Sales figure is $2,276,898,965.01

% Sales Market Share figure is 44.40%