Calculated column to work on Filtered Time / Date Range

[ibuhary]

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 ?

[ibuhary · Accepted solution]

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

[ibarrau]

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]

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]

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

[TomMartens]

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]

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]

[TomMartens]

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]

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.

[TomMartens]

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

[ibuhary]

Google Drive Power BI File

Here is the link to download the file.

If you see in my reports page, the  Right Bottom most table, I have applied a date filter.

I want to show in there 100% for the product for the selected date filter.