Forum Discussion

JillHenninger's avatar
JillHenninger
Helper I
5 years ago

Fair Share Calculation

Hi all.

I am trying to create a series of calculations using Dax, and I am stuck on a piece.

Context: First, I need to calc market share - how much is our account selling of a specific product within the entire geography. Then I would compare that to how much the total account sells in their geopgrahy (aka Fair Share). Then I calculate a gap between those two to see if the account is selling less or more than their share of the product.

Pieces: 

Market Share = 
var _numerator = CALCULATE(sum(Account[Dollars]))
var _denominator = [Total Market Dollars]
return DIVIDE(_numerator, _denominator)

 

Current "Fair Share" formula:

FairShare = CALCULATE([Market Share],ClientList[Client]="Total Departments")

Result:

 

 

 

 

 

 

 

 

 

The formula won't go down. Now, in this table, there is a duplication of the products listed below

 

 

 

 

 

 

I've also tried this version of the formula (the table is filtered using the Representation column):

FairShare = CALCULATE([Market Share],ALL(ClientList[Client]),ClientList[Client]="Total Departments",REMOVEFILTERS(Representation[Representation]))

 Any ideas?? Thank you!

Need Help

7 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi JillHenninger ,

     

    Try the following formula:

     

    Measure = 
SUMX(
    FILTER(
        ALLSELECTED(ClientList[Client]),
        ClientList[Client] = "Total Departments"
    ),
    [Market Share]
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JillHenninger's avatar
      JillHenninger
      Helper I
      5 years ago

      Thank you! Alas, that formula have me the exact same result (14.8%) as before and as before, only for the top row...

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi JillHenninger ,

         

        Could you please share the relationship between the tables in the Model view?

         

        In your Fair Share formula I see that you have restrictions on client

                                             ClientList[Client]="Total Departments",

        so the calculation result you want is consistent (14.8%)?

         

         

        Best Regards,
        Winniz

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Does this measure work?

    FairShare = CALCULATE([Market Share],all(ClientList[Client]))

    If it does not work, then share the link from where i can download your PBI file.

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi JillHenninger ,

     

    Has your problem been solved? 

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

     

    Best Regards,
    Winniz