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]=EARL...
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...

TomMartens's avatar
TomMartens
Super User
7 years ago

Hey, 

 

I'm not sure if I understand you correctly, but maybe this little chart helps to clarify things.

 

What am I trying to explain with this image is the following, a calculated column will be recalculated on data refresh. This means when you use a slicer the rows that contains the calculated column will be filtered, without recalculating the column based on the filterd rows, for this reason you have to create a measure, because a measure will be recalculated each time a DAX query is issued, e.g. a slicer selection is made by the user.

 

Hopefully this explains why the "SUM" of the calculated column will be below 100%.

 

Regards,

Tom

ibuhary's avatar
ibuhary
Helper I
7 years ago

I understand the concept of filter and slicing visuals.

 

at the moment I am looking for a way to drop my calculated column and go for a new Measure.

 

Please see my previous reply for more details.

  • ibuhary's avatar
    ibuhary
    Helper I
    7 years ago

     

    • TomMartens's avatar
      TomMartens
      Super User
      7 years ago

      Hey,

       

      it was not clear to me that you were looking for a measure, at least not to me.

       

      maybe this measure provides what you are looking for:

       

      p100 = 
DIVIDE(
    CALCULATE(
        SUM(Table1[amount])
    )
    ,CALCULATE(
        SUm('Table1'[amount])
        ,ALLSELECTED('Table1')
    )
,BLANK()
)

       

       

      This leades to a report that looks like this:

       

       

      If this is not what you are looking, you might consider to create a pbix file that contains the data shown in the screenshot of your last post, upload the pbix to onedrive or dropbox and share the link.

       

      Regards,

      Tom

      • ibuhary's avatar
        ibuhary
        Helper I
        7 years ago

        It seems close to what I look for. But needs more tweeks.

         

        The percentages should sum up to 100% by each product.

         

        Thats why I call the measure Market Share of Sales by "Product", and not just Market Share by Sales.