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

Market Share with a fixed value

Hi,

 

I'm trying to calculate Market Share without taking into account mix effect between Key Accounts and i'm stuck. i can do the calculations in Excel but not in DAX. 

i've got a table similar to this one (with more columns: products, size, etc):

PeriodKey AccountManufacturerSales Vol
JanuaryKA 1A100
JanuaryKA 1B50
JanuaryKA 2A50
JanuaryKA 2B50
JanuaryKA 3A50
JanuaryKA 3B50
FebruaryKA 1A110
FebruaryKA 1B70
FebruaryKA 2A55
FebruaryKA 2B50
FebruaryKA 3A55
FebruaryKA 3B50

 

So, Share taking into account sales mix between channels would be:

ManufJanuary ShareFebruary ShareVar W/Mix
A57%56%-0,7%
B43%44%0,7%

 

share A in January = A total vol in January / total vol in January

But i want to get market share variation for February without taking into account intra KA effect for Manufacturer A:

Share AJanuaryFebruaryVariation W/MIXWeight KA FebVar WO/Mix 
KA 167%61%-6%46%-2,6% 
KA 250%52%2%27%0,6% 
KA 350%52%2%27%0,6% 
     0,5%Intra KA mix
     -0,7% 

 

In the example, as KA 1 increased their total sales volume participation in February and as Manuf A Share in that KA it's above average Share, the loss of market share in that KA would represent -2,6%.
-6%=January Share - February Share
-2,6%= (january share - february share) * weight KA Feb -> this is the formula i need

 

This is the formula i'm using for market share with mix:
Share = 'Base'[Sales Vol] / 

CALCULATE(
SUM(
'Base'[Sales Vol]
);
FILTER(
ALL('Base');
'Base'[Period]=EARLIER('Base'[Period])
)
)
 
I hope i'm being clear enough to get an answer. thanks in advance!
 
regards
5 REPLIES 5
jor062 Frequent Visitor
Frequent Visitor

Re: Market Share with a fixed value

I would really appreciate help 🙂 

 

Community Support Team
Community Support Team

Re: Market Share with a fixed value

Hi @jor062 ,

I can't understand how did you get the following result:
msedge_2pa67JOuLL.png

The sum of data 67%, 50%, 50% is more than 100%

Best Regards,

Teige

jor062 Frequent Visitor
Frequent Visitor

Re: Market Share with a fixed value

@TeigeGao it's only the share of Manufacturer A in those KA. to get 100% for each KA we have to add manuf B.

 

for getting 100% i should do same calculations as column weight:

   Weight
Share AJanuaryFebruaryJanuaryFebruary
KA 128,6%28,2%50,0%50,0%
KA 214,3%14,1%25,0%25,0%
KA 314,3%14,1%25,0%25,0%
 57%56%100%100%

 

i don't know if i can share PBIX or XLS files over here to show you where i'm stuck, it's simpler than copying the tables and not being able to show the formulas. 

Let me konw if it's clearer now my problem. thanks for your time!

 

jor062 Frequent Visitor
Frequent Visitor

Re: Market Share with a fixed value

help please 😞 

jor062 Frequent Visitor
Frequent Visitor

Re: Market Share with a fixed value

help!!

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 2,676 guests
Please welcome our newest community members: