Market share calculation - how to add filters ?

[JaouadZrhibi]

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

[v-cherch-msft · Accepted solution]

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

[JaouadZrhibi]

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.

Thank you for your help !

Jaouad

[JaouadZrhibi]

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

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]

Hi Cherie,

Thank you very much for you help !

Have a nice day,

Jaouad