How to calculate market share per week and use line chart to visualise

[NonWhizKid]

Hi all,

I'm in need of help to calculate a dynamic total to use in a line chart so I can calculate market share depending on which market OR markets I choose.

My data set looks like this:

Market	Week	Item	Value	Volume
Europe	Week 1 - 5/5/20	TestItem1	500	20
Europe	Week 2 - 12/5/20	TestItem1	250	10
Europe	Week 3 - 12/5/20	TestItem2	300	15
Africa	Week 1 - 5/5/20	TestItem3	500	50

And so on. There are 15 different markets, 40 different week periods and >1000 items.

The first problem is how the time is being report. I've used left/right/mid in order to create a time format that works and created a Calendar table. So far, so good.

Next, create a measure to calculate the Items - I've only used the sum-function as it works (measure1=sum('Table1',[Value])). 

Next, to calculate what all items in a week totals to I've created a measure = calculate(sum('Table1',[Value]),Allselected('Table1')) and it does the trick. In two different slices and can choose markets and time period and the values updates. For market share percentages I'm using divide(measure1,measure2,0).

However, on a separate page, when I try and create a line chart visual with time periods on the X-axis and Market share on Y-axis, the market share comes out as a total of all 40 weeks. So instead of 50% market share in week 3, it turns out like 1,2%. 

My idea to overcome this is to create a measure which utilises "week" as a filter, but I can't get it to work.

Previously, when there was no need for dynamic calculations, I've added a column using "Filter" and "Earlier" for both Market and Week, but that doesn't help me know.

Sorry for my poor explanation but I hope you understand what I'm trying to achieve.

Thanks!

[danextian · Accepted solution]

Hi @NonWhizKid ,

If you want to create a weekly total that is not affected by the dates within that week, use ALLEXCEPT. Sample formula would be

=
CALCULATE ( [Measure], ALLEXCEPT ( Dates, Dates[Week] ) )

The formula above assumes that you have a separate dates table which I think you do as you mentioned that you created a calendar table.

If this is not what you're looking for, please provide a sample data (not an image) that is the best rerpresentation of your actual data and the sample results based on the sample data provided. There are instances when an OP provides sample data but the expected result is not based on the data provided.