cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ibuhary Regular Visitor
Regular Visitor

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
ibuhary Regular Visitor
Regular Visitor

Re: Calculated column to work on Filtered Time / Date Range

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 11
ibarrau Established Member
Established Member

Re: Calculated column to work on Filtered Time / Date Range

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 Regular Visitor
Regular Visitor

Re: Calculated column to work on Filtered Time / Date Range

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.
Super User
Super User

Re: Calculated column to work on Filtered Time / Date Range

Hey, 

 

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

1-2 How DAX interacts with the tabular data model.png

 

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
ibuhary Regular Visitor
Regular Visitor

Re: Calculated column to work on Filtered Time / Date Range

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 Regular Visitor
Regular Visitor

Re: Calculated column to work on Filtered Time / Date Range

Data.jpg

 

Highlighted
ibarrau Established Member
Established Member

Re: Calculated column to work on Filtered Time / Date Range

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

Super User
Super User

Re: Calculated column to work on Filtered Time / Date Range

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:

 image.png

 

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
ibuhary Regular Visitor
Regular Visitor

Re: Calculated column to work on Filtered Time / Date Range

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.

 

 

Super User
Super User

Re: Calculated column to work on Filtered Time / Date Range

Hey,

 

I guess then you should consider to an xlsx with sample data, a sheet that contains the data and onee or more sheets that should reflect your filtering and the result you expect. Put that sheet to onedrive or dropbox and share the link

 

Your screenshot put a lot of strain to me, to create exactly what you want.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!