Forum Discussion

ibuhary's avatar
ibuhary
Helper I
7 years ago
Solved

Calculated column to work on Filtered Time / Date Range

Hello Everyone,

 

I currently have the following calculated column:

 

MktShareProduct =Table3[Traded Volume]/CALCULATE(SUM(Table3[Traded Volume]),ALLSELECTED(),FILTER(Table3,Table3[Product2]=EARLIER(Table3[Product2])))

 

The objective is to create a market share measure for which the total should always be 100% on each product for any filter with other dymentions.

 

It works well with all dymentions except time preiod. The problem is when I select or slice for one month the Total percentage for each product doesnt add up to 100%.

 

How do I tell dax to base the total amount on selected period ?

Need Help
  • ibuhary's avatar
    ibuhary
    7 years ago

    I managed to solve it guys,

     

    Here is the new Calculated Measure:

     

    MktShareProductNew = DIVIDE(sum(Table3[Sales]),CALCULATE(SUMX(Table3,Table3[Sales]),ALLEXCEPT(Table3,Table3[Product2],Table3[Clear Year-Month])),BLANK())

     

    Thanks for all the time from you...

11 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    7 years ago

    Hi. Even I can't understand what you are trying to do in your description I think I can help. It seems to me you should check "ALLEXCEPT" function by month to add that instead of ALLSELECTED.

     

    Regards,

    • ibuhary's avatar
      ibuhary
      Helper I
      7 years ago
      Actually i was trying to create a market share measure by product and ended up Creating a calculated column instead.

      I have columns for store, customer, date, product and sales in my table. I need to calculate a market share by product measure. it should give me total of 100% by any dymention provided I dont have any filter for products.

      If i chose to tabulate stores by product for a given month or customer by product for a year always it should give me a total of 100%. But if i filtered by any products the percentage will be below 100.
      • ibarrau's avatar
        ibarrau
        Super User
        7 years ago

        Ok, i continue with my previous post. You have X / Y and you need to have the "Y" with all products always and the X with the product filtered. The other dimensions should affect both X and Y. In order to get this, try this on Y = CALCULATE( Agregation, ALLEXCEPT(Table, Table[Year], Table[Month], Table[Customer]) )

        This way you will have 100% on a filtered month, year, customer but if you just select a product the 100% will change to its percentage.

         

        Regards

  • ibuhary's avatar
    ibuhary
    Helper I
    7 years ago

    I managed to solve it guys,

     

    Here is the new Calculated Measure:

     

    MktShareProductNew = DIVIDE(sum(Table3[Sales]),CALCULATE(SUMX(Table3,Table3[Sales]),ALLEXCEPT(Table3,Table3[Product2],Table3[Clear Year-Month])),BLANK())

     

    Thanks for all the time from you...