Year over year sales calculation

[CloudHerder]

I have a DAX calculation that I can't get to work. The idea is this:

For a vendor in a given market, how much would the market have changed without them?

The trick is, the market is whatever combination of region, country, category, service, and vertical is selected in the current filter context. I only want to evaluate a vendor in the markets where they compete - not the global market. The idea is to calcute the specific market with and without the vendor, for both the current and prior years. The formula is:

Vendor Market Contribution = [EndYear Revenue] - ([BeginYear Revenue] * [EndYear Revenue minus Vendor]/[BeginYear Revenue minus Vendor])

I've tried adding columns to my table that define the Total Revenue for the combination of region, country, category, service, and vertical, for all vendors who compete in that market. That works at a row level, but when I sum them up I get a total market size that's multiplied by the number of vendors in the market. I'm tearing my hair out on this one.

My .pbix file is at https://1drv.ms/u/s!AgIj2L_vt8WrhMFXPfq8XjI72B0dWA?e=5pXeBe.

If you need to relink to the source, it's available at https://1drv.ms/x/s!AgIj2L_vt8WrhMFWmuvGsqSzlWkJ6g?e=6a1c05

Any help - including ideas about better ways to accomplish my goal - is very much appreciated!

[amitchandak]

@CloudHerder , Change the measure like

Savings = sumx(SUMMARIZE('Query1 (3)', 'Query1 (3)'[Date],'Query1 (3)'[Country], 'Query1 (3)'[Plant], 'Query1 (3)'[Brand], 'Query1 (3)'[Package],'Productivity Central'[Real start date], "_1" ,[Cost Tons initial] - [Cost Tons final]), [_1])

[CloudHerder]

@amitchandak I'm not sure if your reply is meant for me, or if you had multiple questions open. It doesn't seem to apply to my question.

[amitchandak]

@CloudHerder , Sorry, That was not for you. Seems like by mistake closed the window and pasted on another window.

[CloudHerder]

That's what I suspected. Of course, you're still welcome to have a look at my challenge 😉