How to make Contribution to Growth formula

[Anonymous]

Hi,

I want to calculate a measure where I sum up the growth of all products that have growth vs. last year and in another measure, I want to aggregate the decline of all the products that have a decline. 

I've tried with

Growth=If([This Year] > [Last year]; [This Year]-[Last Year]) 

Decline=If[This Year]<[Last year];[This Year]-[Last Year])

It works on an aggregated level but it sums up the whole year and not per product. I would like to see both how much that is growth and how much that is decline...

Hope you can help

[Greg_Deckler]

Add Product to your visual?

[Anonymous]

Thanks, @Greg_Deckler, I wished it was that simple - The visualization I use is a Stacked Column where I have growth, decline and value last year in three different column values and then Value this year in a Line, The 3 columns add up to this year's value.

When I have more than one value in Column values I cannot have anything in Column series...

On the chart above I should have both growths and decline in each period - Now I only have the net growth or decline.

In Excel I can fix it, so it should look something like this:

[v-qiuyu-msft]

Hi @Anonymous,

It's expected that we can't add column to Column Series property if we already add more than one fields into Column Values section.

In your scenario, please share some sample data and show us the desired results, so we can test to see if your requirement can be achieved.

Best Regards,
QiuyunYu

[Anonymous]

Thanks for coming back @v-qiuyu-msft.

You can find some data here:

https://www.dropbox.com/sh/vtld3ulkzqmi1ym/AAAxsCvX1DDnCa20e4WVsZ-0a?dl=0

I have been thinking - if I can add a column in the table that looks up last years sales on each SKU, Year, Period it should be easy. There is only one row per SKU/Period....

Thanks for looking into this!

Kent

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

You can create a calculated column like below to get values in last year same weeknr for the same product:

LastYearSameSKU-WEEKNR = LOOKUPVALUE(data[Value Sales'000],data[PRODUCT],'data'[PRODUCT],data[WEEKNR],'data'[WEEKNR],data[YEAR],'data'[YEAR]-1)

Best Regards,
QiuyunYu

[Anonymous]

Thank you very much @v-qiuyu-msft - It worked perfectly!