DAX to calculate Value share

[IliaS]

Hi! 

I am trying to calculate market share, but struggling with doing it correctly.

I have a matrix where I have Category, Name as rows, Channel as column, and Market Share as value.

Also In my dataset I have columns ABS_COMPANY (with sales inputted there if company = "A", so there are some blank ones), and ABS_TOTAL (with sales inputted in each row)

so my measure Market Share:

Market Share = SUM(table[ABS_COMPANY]) / SUM(table[ABS_TOTAL])

This correctly calculates values for each Category, but when I open the drop-down to see Name, Market Share of each Name equals to 100%. What is the problem and how to fix it?

e.g. What is now:

                    Market Share

Pens                  43%
     pen 1           100%

     pen 2           100%

     pen 3           100%

Pencils               29%

     penc 1          100%

     penc 2          100%

     penc 3          100%

expected to calculate market share for each name, not only for categories correctly.

[kamberbi]

It's difficult to help you without more information.

Could you provide more information on the data model?

If you want to help yourself I would try to see, what the value is for each SUM part of the division and then see if both columns gives a wierd number.

Sum of ABS_Company := SUM(table[ABS_COMPANY])
Sum of ABS_Total := SUM(table[ABS_TOTAL])

My hypothesis would be that Name does not have a proper relationshop in the data model.

[IliaS]

Hi! Unfortunately I cannot share data as it is sensitive and generation sample one will take a lot of time. What do you mean by "name does not have a proper relationship in the data model"? I have the single table with all the data I describe, what relationship should I have? I've tried to build hierarchy on Fields pane, but this does not help