Year over year percentage for various products

[chunkysoup]

Hello,

I have found many other similar questions and have tried the solutions contained within those but to no avail.

Below is my data. I would like to create a chart that shows the percentage of growth/decline of a product line year over year.

I am assuming I would have to create a measure for each product line individually unless DAX can lookup what is in the Values field? That is step 1001. I just need to get past Step 1 for now and create a measure that actually works. I have tried the quick measures but it just always shows as Zero and I can't even bring in the Year Column. I had to create a seperate calendar and bring that in and even then I couldn't bring in the year, I had to create days first and determine the year and then I could create the relationship but bringing into the quick measure I could only bring in days...not years.

I am new to measures and more than a little stumped on this as I learn all the syntax as well. Thanks ahead of time for any support you can give.  Cheers!

[Anonymous]

You might want to consider an alternative solution where you won't have to create a measure for each product line.

If you unpivot your data table, things become much easier.UnpivotRename column

You end up with this:

If you than create a measure like:

Measure = 
var CurYear=max(Table2[Year])
var PrevYear=CurYear-1
var curValue= CALCULATE(sum(Table2[Value]);Table2[Year]=CurYear)
var PvValue = CALCULATE(sum(Table2[Value]);Table2[Year]=PrevYear)
var ratio = DIVIDE(PvValue-curValue;curValue)
return
if(COUNTROWS(values(Table2[Year]))=1;ratio)

You'll be able to view information for all product lines like so:

Final Result

I made changes to the previously shared pbix: Year over year percentage for various products.pbix

[v-danhe-msft · Accepted solution]

Hi @chunkysoup,

Based on my test, you could refer to below formula:

YOY for Line1 = var a=MAX([Year])-1
var b=CALCULATE(SUM(Table1[Product Line1]),FILTER(ALL('Table1'),'Table1'[Year]=a))
return DIVIDE(b-CALCULATE(SUM(Table1[Product Line1])),CALCULATE(SUM(Table1[Product Line1])))

Result:

For different line, you just need to modify the formula to replace the [Product Line1]. Also you could download the pbix file to have a view.

Regards,

Daniel He

[chunkysoup]

Thank you so much! @v-danhe-msft I actually reversed it to show the decline. Now I just need to remove the 100% and show it as 0 otherwise all the charts look like they all decline right off the bat when a number of them grow