topic Re: Year over year sales calculation in DAX Commands and Tips

Year over year sales calculation

Anonymous — Tue, 18 May 2021 04:30:07 GMT

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!

Re: Year over year sales calculation

amitchandak — Tue, 18 May 2021 07:01:58 GMT

Anonymous , 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])

Re: Year over year sales calculation

Anonymous — Tue, 18 May 2021 15:56:24 GMT

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.

Re: Year over year sales calculation

amitchandak — Tue, 18 May 2021 16:00:59 GMT

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

Re: Year over year sales calculation

Anonymous — Tue, 18 May 2021 17:04:12 GMT

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