Calculating Market Share / Percentage of total

[scass]

Hi all,

I've been pottering around Power BI Desktop for a week or so now so please excuse the potentially basic question I have here.

How do I calculate a measure showing market share of products sold?
Much like the Sales and Marketing Sample;
https://powerbi.microsoft.com/en-us/documentation/powerbi-sample-sales-and-marketing-take-a-tour/
 
my table is roughly as follows;
PK | Company_Name | State_Name | Product_Type | Volume Sold

I want to have the option of disecting the market share by company, state, product type etc.

I'm at a loss and any help would be greatly appreciated. Thanks in advance;

[PierreBAT]

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 volumes of a given Week or Month or Quarter DATA POINT, 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

[JoHo_BI]

Hi Scass!

From my understanding of your issue, you need to create a measure in that table and use the following formula:

Market Share = CALCULATE(SUM(TABLENAME[Volume Sold]),Company_Name = "Company1")/SUM(TABLENAME[Volume Sold])

The CALCULATE function let's you apply a filter after an expression, so in this case Company Name. This can of course be changed at your discretion. 

The slicer visualisation on the bottom left of the visualisations pane is a great way to easily slice through data! Simply add your chosen filter to the 'field' box and you'll be presented with a simply filtering tool for your entire page. You can have multiple of these on the page too. 

Hope this is of some use. Best of luck in your future Power BI adventures!

[scass]

Thanks for your reply @JoHo_BI 
Is there a way in which I can have it as a generic vairable for all companies rather than just one?

I'd like to put the [market share] variable as the value on a graph with [Company_Name] on the axis

[JoHo_BI]

No problem!

As far as my understanding goes, you need at least one static variable at leasst for the filter function, so try to make that the top level variable such as company name. 

Otherwise, you could just have a graph with the value showing the sum of product sales with the axis showing company name. This won't give you the desired percentage, in this case you'd have to copy and paste the measure as a card or somewhat and change the filter to each Company Name. 

[scass]

This is making me think I need to do this from the DB side and not in Power BI.
I don't know why or what I'm not getting ... maybe i'm just being slow today...and yesterday.

It annoys me that the sample (see link above), seems to have it all working fine, yet I can't figure out how they do it, I can't see the data mapper or the raw data, otherwise I could reverse engineer it.

[konstantinos · Accepted solution]

@scass  There is no need to do that in DB.

There are two ways, UI and DAX.

UI way: Create the graph you like ( not applicable to tables) -> select the arrow in the visualizations pane - values section of your measure -> Quick calc -> opens new window - > Pct of total 

Dax formula 

Market Share =

VAR Volume =
    SUM ( TableName[volume] )
VAR AllVolume =
    CALCULATE ( SUM ( TableName[volume] ); ALL ( TableName[Company] ) )

RETURN
    DIVIDE ( Volume; AllVolume )

The market share adjusts as pct of total , when you filter by Product or state. If you need to stay always the same in ALL() function add only the TableNAme not the column name

Hope it helps

[moatazezat]

NOT APPER WITH ME THE TOTAL SALES 

[lushd]

This is outdated. Please unmark it as solution. The 'quick calc' button does not exist anymore. 

[lushd]

It is now "SHOW VALUE AS" > "PERCENTAGE OF GRAND TOTAL"

[moatazezat]

thanks for your reply, but I need a PERCENTAGE OF the total Column 

[SA_NYC]

Fantastic explanation, much appreciated! The clearest demonstration of the use of variables to solve this that I've come across. I'd note that for people like me located in North America, use a comma instead of a semi-colon (as I learned the hard way). Thank you!

[scass]

Amazing, thank you ever so much!

Are the semi colons supposed to be commas?