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

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

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 :-)

2 ACCEPTED SOLUTIONS

Accepted Solutions
SamLester Established Member
Established Member

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

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)

simfr Frequent Visitor
Frequent Visitor

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

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))

10 REPLIES 10
SamLester Established Member
Established Member

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

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)

simfr Frequent Visitor
Frequent Visitor

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

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?

simfr Frequent Visitor
Frequent Visitor

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

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))

TobiasF Frequent Visitor
Frequent Visitor

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

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))

SonaliG_LeX Regular Visitor
Regular Visitor

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

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.

readytolearn Frequent Visitor
Frequent Visitor

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

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. 

Franz_Valverde Frequent Visitor
Frequent Visitor

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

% 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!

PierreBAT Frequent Visitor
Frequent Visitor

Power BI - Market Share Calculation with different variables

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

houston39 Frequent Visitor
Frequent Visitor

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

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%