cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JaouadZrhibi Regular Visitor
Regular Visitor

Market share calculation - how to add filters ?

Hi,

I am a beginner and I can't solve a small issue in my formula on Market Share measure.

 

The formula "mixes" both import and export trades :

 

Market Share = DIVIDE(CALCULATE(SUM([Tons]));CALCULATE(SUM([Tons]);ALL('Details')))

File is here : https://drive.google.com/open?id=1sRFKhMPu1LSjMtk58_Z7e_B1BTHhn5FS

 

I think that we need just one formula with one filter for IMPORT trade and a another one for EXPORT trade.

Thank you very much for your help and support.

 

Have a nice day,

 

Jaouad

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Market share calculation - how to add filters ?

Hi @JaouadZrhibi

 

You may try to use below measure:

Market Share =
DIVIDE (
    CALCULATE ( SUM ( [Tons] ) ),
    CALCULATE ( SUM ( [Tons] ), VALUES ( Details[Direction] ), ALL ( Details ) )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: Market share calculation - how to add filters ?

Hi @JaouadZrhibi

 

You may try to use below measure:

Market Share =
DIVIDE (
    CALCULATE ( SUM ( [Tons] ) ),
    CALCULATE ( SUM ( [Tons] ), VALUES ( Details[Direction] ), ALL ( Details ) )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JaouadZrhibi Regular Visitor
Regular Visitor

Re: Market share calculation - how to add filters ?

Hi Cherie,

 

Thank you very much for you help !

 

Have a nice day,

 

Jaouad

Highlighted
JaouadZrhibi Regular Visitor
Regular Visitor

Re: Market share calculation - how to add filters ?

Hi Cherie,

 

The measure works well but how can we fix the year's filter ?

 

I mean for each year selected, we want 100% market share.

 

Target = calculate for the market share on a selected year as a reference.

 

For example, if we choose only 2017, we need to have 100% market share.

 

Also, if we choose 2 or more years like 2017, 2016 and 2015, we need to have 100% market share.

 

https://drive.google.com/open?id=1sRFKhMPu1LSjMtk58_Z7e_B1BTHhn5FS

 

Please how can we fix this "issue".

 

Thank you for your support,

 

Jaouad

JaouadZrhibi Regular Visitor
Regular Visitor

Re: Market share calculation - how to add filters ?

Hi Cherie,

 

I have tried different measures but I cant see the distribution over a single year.

 

My formula calculate the market share for all years from 2001 till 2018 but how to do it for only one year ?

 

one year for one direction (import or export) = 100% market share 

 


Market share issue_Distribution over 1 year.jpg

 

 

Market Share Import = DIVIDE(CALCULATE(SUM([Tons]));CALCULATE(SUM([Tons]);ALL('Details'); Table[Column] = "Import")) 

Market Share Export = DIVIDE(CALCULATE(SUM([Tons]));CALCULATE(SUM([Tons]);ALL('Details'); Table[Column] = "Export")) 

 

OR 

 

Market Share = DIVIDE ( CALCULATE ( SUM ( [Tons] ) ), CALCULATE ( SUM ( [Tons] ), VALUES ( Details[Direction] ), ALL ( Details ) ) )

 

 

PBIX file =  https://drive.google.com/open?id=1sRFKhMPu1LSjMtk58_Z7e_B1BTHhn5FS

 

Thank you for your help and support,

 

Jaouad

JaouadZrhibi Regular Visitor
Regular Visitor

Re: Market share calculation - how to add filters ?

Hi @v-cherch-msft

 

I thank you for your measure but I need to separate Import & Export markets.

 

I would like to get 100% market share if we select only IMPORT.

 

Also 100% market share if we select only EXPORT.

 

In fact, it isn't pertinent to "mix" import & export markets because they are different markets.

 

File is here

https://drive.google.com/open?id=1sRFKhMPu1LSjMtk58_Z7e_B1BTHhn5FS

 

Please see the pictures below. Hope they will help you.

 

One year and Import selected.png

 

One year and EXPORT selected.jpg

 

 

Thank you for your help !

 

Jaouad